Le sql, c'est vachement simple
EsKel
-
soos -
soos -
On ne peut plus simple, est-ce qu'il est possible de simplifier ca ??
SELECT tblUser.lngUserID, tblUser.txtUserNickName, tblUser.txtUserEmail, tblOccupation.txtOccupation, tblState.txtSate, tblRegion.txtRegion, DATEDIFF(year,
tblUser.dteUserBirthday, GETDATE()) AS intAge, ISNULL(tblTotInterest.intTotInterest, 0) AS intSumInterest,
ISNULL(tblTotPersonnality.intTotPersonnality, 0) AS intSumPersonnality, ISNULL(tblTotPersonnality.intTotPersonnality, 0)
+ ISNULL(tblTotInterest.intTotInterest, 0) AS intTotOption
FROM (SELECT tblProfile.lngProfileID, COUNT(*) AS intTotInterest
FROM tblProfile INNER JOIN
tblProfileInterest ON tblProfile.lngProfileID = tblProfileInterest.lngProfileID
GROUP BY tblProfile.lngProfileID) tblTotInterest FULL OUTER JOIN
(SELECT tblProfile.lngProfileID, COUNT(*) AS intTotPersonnality
FROM tblProfile INNER JOIN
tblProfilePersonnality ON tblProfile.lngProfileID = tblProfilePersonnality.lngProfileID INNER JOIN
tblPersonnality ON tblProfilePersonnality.lngProfileID = tblPersonnality.lngPersonnalityID
GROUP BY tblProfile.lngProfileID, tblProfile.lngUserID) tblTotPersonnality RIGHT OUTER JOIN
tblUser INNER JOIN
tblProfile ON tblUser.lngUserID = tblProfile.lngUserID INNER JOIN
tblSearchProfile ON tblUser.lngUserID = tblSearchProfile.lngUserID INNER JOIN
tblSearchProfileType ON tblSearchProfile.lngSearchProfileID = tblSearchProfileType.lngSearchProfileID INNER JOIN
tblRegion ON tblUser.lngRegionID = tblRegion.lngRegionID INNER JOIN
tblOccupation ON tblUser.lngOccupationID = tblOccupation.lngOccupationID INNER JOIN
tblState ON tblRegion.lngStateID = tblState.lngStateID ON tblTotPersonnality.lngProfileID = tblProfile.lngProfileID ON
tblTotInterest.lngProfileID = tblTotPersonnality.lngProfileID
GROUP BY ISNULL(tblTotInterest.intTotInterest, 0), ISNULL(tblTotPersonnality.intTotPersonnality, 0), tblOccupation.txtOccupation, tblOccupation.lngOccupationID,
tblRegion.txtRegion, DATEDIFF(year, tblUser.dteUserBirthday, GETDATE()), tblUser.txtUserEmail, tblUser.lngUserID,
ISNULL(tblTotPersonnality.intTotPersonnality, 0) + ISNULL(tblTotInterest.intTotInterest, 0), tblUser.txtUserNickName, tblState.txtSate,
tblRegion.lngRegionID, tblRegion.lngStateID
ORDER BY ISNULL(tblTotInterest.intTotInterest, 0) DESC, ISNULL(tblTotPersonnality.intTotPersonnality, 0) DESC
SELECT tblUser.lngUserID, tblUser.txtUserNickName, tblUser.txtUserEmail, tblOccupation.txtOccupation, tblState.txtSate, tblRegion.txtRegion, DATEDIFF(year,
tblUser.dteUserBirthday, GETDATE()) AS intAge, ISNULL(tblTotInterest.intTotInterest, 0) AS intSumInterest,
ISNULL(tblTotPersonnality.intTotPersonnality, 0) AS intSumPersonnality, ISNULL(tblTotPersonnality.intTotPersonnality, 0)
+ ISNULL(tblTotInterest.intTotInterest, 0) AS intTotOption
FROM (SELECT tblProfile.lngProfileID, COUNT(*) AS intTotInterest
FROM tblProfile INNER JOIN
tblProfileInterest ON tblProfile.lngProfileID = tblProfileInterest.lngProfileID
GROUP BY tblProfile.lngProfileID) tblTotInterest FULL OUTER JOIN
(SELECT tblProfile.lngProfileID, COUNT(*) AS intTotPersonnality
FROM tblProfile INNER JOIN
tblProfilePersonnality ON tblProfile.lngProfileID = tblProfilePersonnality.lngProfileID INNER JOIN
tblPersonnality ON tblProfilePersonnality.lngProfileID = tblPersonnality.lngPersonnalityID
GROUP BY tblProfile.lngProfileID, tblProfile.lngUserID) tblTotPersonnality RIGHT OUTER JOIN
tblUser INNER JOIN
tblProfile ON tblUser.lngUserID = tblProfile.lngUserID INNER JOIN
tblSearchProfile ON tblUser.lngUserID = tblSearchProfile.lngUserID INNER JOIN
tblSearchProfileType ON tblSearchProfile.lngSearchProfileID = tblSearchProfileType.lngSearchProfileID INNER JOIN
tblRegion ON tblUser.lngRegionID = tblRegion.lngRegionID INNER JOIN
tblOccupation ON tblUser.lngOccupationID = tblOccupation.lngOccupationID INNER JOIN
tblState ON tblRegion.lngStateID = tblState.lngStateID ON tblTotPersonnality.lngProfileID = tblProfile.lngProfileID ON
tblTotInterest.lngProfileID = tblTotPersonnality.lngProfileID
GROUP BY ISNULL(tblTotInterest.intTotInterest, 0), ISNULL(tblTotPersonnality.intTotPersonnality, 0), tblOccupation.txtOccupation, tblOccupation.lngOccupationID,
tblRegion.txtRegion, DATEDIFF(year, tblUser.dteUserBirthday, GETDATE()), tblUser.txtUserEmail, tblUser.lngUserID,
ISNULL(tblTotPersonnality.intTotPersonnality, 0) + ISNULL(tblTotInterest.intTotInterest, 0), tblUser.txtUserNickName, tblState.txtSate,
tblRegion.lngRegionID, tblRegion.lngStateID
ORDER BY ISNULL(tblTotInterest.intTotInterest, 0) DESC, ISNULL(tblTotPersonnality.intTotPersonnality, 0) DESC
A voir également:
- Le sql, c'est vachement simple
- Iphone 14 simple - Guide
- Simple pdf - Télécharger - PDF
- Simple ocr - Télécharger - Bureautique
- Simple file locker - Télécharger - Sécurité
- Simple comic - Télécharger - Vie quotidienne
4 réponses
J'ai oublie que je vais devoir integre des clauses where pour l'occupation , les interets (tblProfilInterest) , les traits de personnalite (tblProfilPersonnality) , l'age (tblUser), et le type de profil recherche(tblSearchProfileType)