Requête de pagination SQL avec ordre par

J'essaye d'écrire une requête qui tire plusieurs champs et leur assigne des alias. Un des alias est en fait une sum totale de deux champs. C'est en fait mon plus gros problème parce que cet alias est l'un des "champs" possibles à sortinger, ou alors je pourrais juste enlever tous les alias et ne pas avoir ce problème. Quoi qu'il en soit, je dois pouvoir passer dans un domaine où order par programmation. Mais parce que j'ai besoin de faire de la pagination avec SQL, je ne peux pas utiliser la fonction gracieuse de LIMIT et avoir à utiliser une sous-requête.

C'est là que le gros problème entre en jeu. Parce que je dois absolument sélectionner au less 2 colonnes dans la sous-requête (parce que j'ai besoin du champ ID pour le but de limitation, et l'alias total sum par ordre), je ne peux pas utiliser ceci comme un "And ID Not In (sous-requête)" puisque vous pouvez seulement returnner un champ de cette manière. Je vais donc vous montrer mon code, puis expliquer les résultats que je reçois.

SELECT TOP (50) dbo.tblMailList.mail_ID AS Expr1, dbo.tblMailList.mail_NameTitle AS Expr2, dbo.tblMailList.mail_FirstName AS Expr3, dbo.tblMailList.mail_LastName AS Expr4, dbo.tblMailList.mail_Company AS Expr5, dbo.tblMailList.mail_Institution AS Expr6, SUM(dbo.tblItem.item_pr + dbo.tblItem.item_premium) AS Expr7, dbo.tblMailList.mail_Comp_Art, dbo.tblMailList.mail_Comp_IndArt, dbo.tblMailList.mail_Comp_GenAm, dbo.tblMailList.mail_Comp_Fire, dbo.tblMailList.mail_Comp_Jewelry, dbo.tblMailList.mail_Comp_Ceramic, dbo.tblMailList.mail_Interest FROM (SELECT TOP (50) tblMailList_1.mail_ID AS Expr1, tblMailList_1.mail_NameTitle AS Expr2, tblMailList_1.mail_FirstName AS Expr3, tblMailList_1.mail_LastName AS Expr4, tblMailList_1.mail_Company AS Expr5, tblMailList_1.mail_Institution AS Expr6, SUM(tblItem_1.item_pr + tblItem_1.item_premium) AS Expr7, tblMailList_1.mail_Comp_Art, tblMailList_1.mail_Comp_IndArt, tblMailList_1.mail_Comp_GenAm, tblMailList_1.mail_Comp_Fire, tblMailList_1.mail_Comp_Jewelry, tblMailList_1.mail_Comp_Ceramic, tblMailList_1.mail_Interest FROM dbo.tblItem AS tblItem_1 INNER JOIN dbo.tblBidder AS tblBidder_1 ON tblItem_1.item_bidder_number = tblBidder_1.bidder_number AND tblItem_1.item_sale_id = tblBidder_1.bidder_sale_id INNER JOIN dbo.tblMailList AS tblMailList_1 ON tblBidder_1.bidder_mail_id = tblMailList_1.mail_ID WHERE (tblMailList_1.mail_Comp_Art <> '1' OR tblMailList_1.mail_Comp_Art IS NULL) AND (tblMailList_1.mail_Comp_IndArt <> '1' OR tblMailList_1.mail_Comp_IndArt IS NULL) AND (tblMailList_1.mail_Comp_GenAm <> '1' OR tblMailList_1.mail_Comp_GenAm IS NULL) AND (tblMailList_1.mail_Comp_Fire <> '1' OR tblMailList_1.mail_Comp_Fire IS NULL) GROUP BY tblMailList_1.mail_Company, tblMailList_1.mail_Institution, tblMailList_1.mail_LastName, tblMailList_1.mail_FirstName, tblMailList_1.mail_NameTitle, tblMailList_1.mail_ID, tblMailList_1.mail_Comp_Art, tblMailList_1.mail_Comp_IndArt, tblMailList_1.mail_Comp_GenAm, tblMailList_1.mail_Comp_Fire, tblMailList_1.mail_Interest, tblMailList_1.mail_Comp_Jewelry, tblMailList_1.mail_Comp_Ceramic ORDER BY Expr7 DESC) AS tblLimiter INNER JOIN dbo.tblMailList ON NOT (tblLimiter.Expr1 = dbo.tblMailList.mail_ID) INNER JOIN dbo.tblBidder ON dbo.tblBidder.bidder_mail_id = dbo.tblMailList.mail_ID INNER JOIN dbo.tblItem ON dbo.tblItem.item_bidder_number = dbo.tblBidder.bidder_number AND dbo.tblItem.item_sale_id = dbo.tblBidder.bidder_sale_id WHERE (dbo.tblMailList.mail_Comp_Art <> '1' OR dbo.tblMailList.mail_Comp_Art IS NULL) AND (dbo.tblMailList.mail_Comp_IndArt <> '1' OR dbo.tblMailList.mail_Comp_IndArt IS NULL) AND (dbo.tblMailList.mail_Comp_GenAm <> '1' OR dbo.tblMailList.mail_Comp_GenAm IS NULL) AND (dbo.tblMailList.mail_Comp_Fire <> '1' OR dbo.tblMailList.mail_Comp_Fire IS NULL) AND (NOT (dbo.tblMailList.mail_ID = tblLimiter.Expr1)) GROUP BY dbo.tblMailList.mail_Company, dbo.tblMailList.mail_Institution, dbo.tblMailList.mail_LastName, dbo.tblMailList.mail_FirstName, dbo.tblMailList.mail_NameTitle, dbo.tblMailList.mail_ID, dbo.tblMailList.mail_Comp_Art, dbo.tblMailList.mail_Comp_IndArt, dbo.tblMailList.mail_Comp_GenAm, dbo.tblMailList.mail_Comp_Fire, dbo.tblMailList.mail_Interest, dbo.tblMailList.mail_Comp_Jewelry, dbo.tblMailList.mail_Comp_Ceramic ORDER BY Expr7 DESC 

Le problème avec celui-ci est que si la sous-requête sélectionne le top 0, rien n'est renvoyé, ce qui était assez évident, mais je devais essayer parce que j'ai beaucoup essayé. Lorsque 50 est sélectionné, il renvoie 50 lignes avec des totaux SOM importants. Quand 100 est sélectionné, il returnne le même 50 (à cause du top 50 original), mais les totaux de SUM sont deux fois plus gros …

Maintenant, j'ai aussi eu ceci à l'endroit où l'instruction "FROM" a été inversée, dans le même ordre que ce qui est dans la sous-requête elle-même, comme ceci:

 SELECT TOP (50) tblMailList.mail_ID AS Expr1, tblMailList.mail_NameTitle AS Expr2, tblMailList.mail_FirstName AS Expr3, tblMailList.mail_LastName AS Expr4, tblMailList.mail_Company AS Expr5, tblMailList.mail_Institution AS Expr6, SUM(tblItem.item_pr + tblItem.item_premium) AS Expr7, tblMailList.mail_Comp_Art, tblMailList.mail_Comp_IndArt, tblMailList.mail_Comp_GenAm, tblMailList.mail_Comp_Fire, tblMailList.mail_Comp_Jewelry, tblMailList.mail_Comp_Ceramic, tblMailList.mail_Interest FROM dbo.tblItem AS tblItem INNER JOIN dbo.tblBidder AS tblBidder ON tblItem.item_bidder_number = tblBidder.bidder_number AND tblItem.item_sale_id = tblBidder.bidder_sale_id INNER JOIN dbo.tblMailList AS tblMailList ON tblBidder.bidder_mail_id = tblMailList.mail_ID LEFT OUTER JOIN (SELECT TOP (50) tblMailList_1.mail_ID AS Expr1, tblMailList_1.mail_NameTitle AS Expr2, tblMailList_1.mail_FirstName AS Expr3, tblMailList_1.mail_LastName AS Expr4, tblMailList_1.mail_Company AS Expr5, tblMailList_1.mail_Institution AS Expr6, SUM(tblItem_1.item_pr + tblItem_1.item_premium) AS Expr7, tblMailList_1.mail_Comp_Art, tblMailList_1.mail_Comp_IndArt, tblMailList_1.mail_Comp_GenAm, tblMailList_1.mail_Comp_Fire, tblMailList_1.mail_Comp_Jewelry, tblMailList_1.mail_Comp_Ceramic, tblMailList_1.mail_Interest FROM dbo.tblItem AS tblItem_1 INNER JOIN dbo.tblBidder AS tblBidder_1 ON tblItem_1.item_bidder_number = tblBidder_1.bidder_number AND tblItem_1.item_sale_id = tblBidder_1.bidder_sale_id INNER JOIN dbo.tblMailList AS tblMailList_1 ON tblBidder_1.bidder_mail_id = tblMailList_1.mail_ID WHERE (tblMailList_1.mail_Comp_Art <> '1' OR tblMailList_1.mail_Comp_Art IS NULL) AND (tblMailList_1.mail_Comp_IndArt <> '1' OR tblMailList_1.mail_Comp_IndArt IS NULL) AND (tblMailList_1.mail_Comp_GenAm <> '1' OR tblMailList_1.mail_Comp_GenAm IS NULL) AND (tblMailList_1.mail_Comp_Fire <> '1' OR tblMailList_1.mail_Comp_Fire IS NULL) GROUP BY tblMailList_1.mail_Company, tblMailList_1.mail_Institution, tblMailList_1.mail_LastName, tblMailList_1.mail_FirstName, tblMailList_1.mail_NameTitle, tblMailList_1.mail_ID, tblMailList_1.mail_Comp_Art, tblMailList_1.mail_Comp_IndArt, tblMailList_1.mail_Comp_GenAm, tblMailList_1.mail_Comp_Fire, tblMailList_1.mail_Interest, tblMailList_1.mail_Comp_Jewelry, tblMailList_1.mail_Comp_Ceramic ORDER BY Expr7 DESC) AS tblLimiter ON tblLimiter.Expr1 > 0 WHERE (tblMailList.mail_Comp_Art <> '1' OR tblMailList.mail_Comp_Art IS NULL) AND (tblMailList.mail_Comp_IndArt <> '1' OR tblMailList.mail_Comp_IndArt IS NULL) AND (tblMailList.mail_Comp_GenAm <> '1' OR tblMailList.mail_Comp_GenAm IS NULL) AND (tblMailList.mail_Comp_Fire <> '1' OR tblMailList.mail_Comp_Fire IS NULL) AND (NOT (tblMailList.mail_ID = tblLimiter.Expr1)) GROUP BY tblMailList.mail_Company, tblMailList.mail_Institution, tblMailList.mail_LastName, tblMailList.mail_FirstName, tblMailList.mail_NameTitle, tblMailList.mail_ID, tblMailList.mail_Comp_Art, tblMailList.mail_Comp_IndArt, tblMailList.mail_Comp_GenAm, tblMailList.mail_Comp_Fire, tblMailList.mail_Interest, tblMailList.mail_Comp_Jewelry, tblMailList.mail_Comp_Ceramic ORDER BY Expr7 DESC 

Cependant, cela fonctionne exactement de la même manière que dans l'autre sens (dans la façon dont les résultats sont renvoyés).

Je souhaite vraiment que je puisse faire un " AND NOT IN (sub query) ", parce que je peux faire ces travaux sans problème. Mais à cause du SUM dans Expr7, je ne peux pas faire ça. Et je suis un mec MySQL, donc je ne sais pas beaucoup de choses sur SQL. J'espère avoir donné assez d'informations. Si non, faites le moi savoir. Merci pour toutes les réponses.

Au lieu d'utiliser TOP 50 pour votre pagination, utilisez ROW_NUMBER , puis utilisez le

 WITH CTE AS ( SELECT ROW_NUMBER() OVER (ORDER BY SUM(dbo.tblItem.item_pr + dbo.tblItem.item_premium) ) RN, dbo.tblMailList.mail_ID AS Expr1, dbo.tblMailList.mail_NameTitle AS Expr2, dbo.tblMailList.mail_FirstName AS Expr3, dbo.tblMailList.mail_LastName AS Expr4, dbo.tblMailList.mail_Company AS Expr5, dbo.tblMailList.mail_Institution AS Expr6, SUM(dbo.tblItem.item_pr + dbo.tblItem.item_premium) AS Expr7, dbo.tblMailList.mail_Comp_Art, dbo.tblMailList.mail_Comp_IndArt, dbo.tblMailList.mail_Comp_GenAm, dbo.tblMailList.mail_Comp_Fire, dbo.tblMailList.mail_Comp_Jewelry, dbo.tblMailList.mail_Comp_Ceramic, ..... ) SELECT * FROM CTE WHERE rn Between 50 and 100