SQL 分页查询 order by

Question

我正在尝试编写一个查询来提取多个字段并为其分配别名。其中一个别名实际上是两个字段的总和。这实际上是我最大的问题，因为该别名是可能进行排序的“字段”之一，否则我可以删除所有别名而不会出现此问题。无论如何，我需要能够传入一个以编程方式排序的字段。但是因为我需要用SQL进行分页，所以我无法使用LIMIT的优雅功能，而必须使用子查询。

这就是大问题出现的地方。因为我绝对必须在子查询中选择至少 2 列（因为我需要 ID 字段用于限制目的，并且总和别名作为 order by 字段），所以我不能使用这作为“并且 ID 不在（子查询）”，因为您只能以这种方式返回一个字段。因此，我将向您展示我的代码，然后解释我得到的结果。

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

这个的问题是，如果子查询选择 top 0，则不会返回任何内容，这是相当明显的，但我需要尝试一下，因为我已经尝试了很多。选择 50 时，将返回 SUM 总计较大的 50 行。当选择 100 时，它返回相同的 50（因为原来的前 50 名），但 SUM 总数是原来的两倍...

现在我也有这个到“FROM”语句被反转的地方，顺序与子查询本身的内容如下：

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

但是，这与其他方式的工作方式完全相同（返回结果的方式）。

我真的希望我可以做一个“AND NOT IN（子查询）”，因为我可以毫无问题地完成这些工作。但由于 Expr7 中的 SUM，我无法这样做。我是一名 MySQL 人员，所以我对 SQL 不太了解。我希望我提供了足够的信息。如果没有，请告诉我。感谢您的所有回复。

Answer 1

不要使用 TOP 50 进行分页，而是使用 ROW_NUMBER，然后使用 where

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