SQL分页查询，按顺序排序 [英] SQL Pagination Query with order by
问题描述
我正在尝试编写一个查询，该查询可提取多个字段并为其分配别名.别名之一实际上是两个字段的总和.这实际上是我最大的问题，因为该别名是排序时可能使用的字段"之一，否则我可能会删除所有别名而不会遇到此问题.无论如何，我需要能够以编程方式传递要订购的字段.但是因为我需要使用SQL进行分页，所以无法使用LIMIT的优美功能，而不得不使用子查询.
I am trying to write a query that pulls multiple fields and assigns aliases to them. One of the aliases is actually a sum total of two fields. This is actually my biggest issue because that alias is one of the possible "fields" to sort by, or else I could just take out all of the aliases and not have this issue. Anyway, I need to be able to pass in a field in which to order by programmaticly. But because I need to do pagination with SQL, I cannot use the graceful function of LIMIT and have to use a sub query.
这是大问题出现的地方.因为我绝对必须在子查询中选择至少2列(因为我出于限制目的需要ID字段，而总别名作为按字段排序)，我不能将其用作"And ID Not In(子查询)"，因为您只能以这种方式返回一个字段.因此，我将向您展示我的代码，然后解释我得到的结果.
This is where the big issue comes in. Because I absolutely HAVE to select at least 2 columns in the sub query (because I need the ID field for the limiting purpose, and the sum total alias as an order by field), I cannot use this as an "And ID Not In (sub query)" since you can only return one field in that manner. So I'll show you my code, then explain the results I am getting.
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
这一问题的原因是，如果子查询选择前0，则不会返回任何内容，这很明显，但是我需要尝试一下，因为我做了很多尝试.选择50时，它将返回50行，且SUM总数较大.选择100时，它返回相同的50(因为原来的前50名)，但是SUM总数是原来的两倍...
The issue with this one is that if the sub query selects top 0, nothing is returned, which was fairly obvious, but I needed to try because I've tried a lot. When 50 is selected, it returns 50 rows with large SUM totals. When 100 is selected, it returns the same 50 (because of the original top 50), but the SUM totals are twice as big...
现在，我也将它放在"FROM"语句被反转的位置，其顺序与子查询本身的顺序相同，如下所示:
Now I also had this to where the "FROM" statement was reversed, in the same order as what is in the sub query itself, like this:
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
但是，这与另一种方法(返回结果的方式)完全相同.
However, this works the exact same way as the other way (in the way the results are returned).
我真的希望我可以做一个"AND NOT IN (sub query)"，因为我可以使这些工作顺利进行.但是由于Expr7中的SUM，我无法做到这一点.而且我是一个MySQL专家，所以我对SQL并不了解很多.我希望我能提供足够的信息.如果没有，请告诉我.感谢您的所有答复.
I really wish I could do a "AND NOT IN (sub query)", because I can make those work without an issue. But because of the SUM in Expr7, I cannot do that. And I'm a MySQL guy, so I don't know a whole lot about SQL. I hope I gave enough information. If not, just let me know. Thanks for all replies.
推荐答案
Instead of using TOP 50 for your pagination use ROW_NUMBER and then use the 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
这篇关于SQL分页查询，按顺序排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
