SQL CTE 和 ORDER BY 影响结果集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3924940/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL CTE and ORDER BY affecting result set
提问by Brett
I've pasted a very simplified version of my SQL query below. The problem that I'm running into is that the ORDER BYstatement is affecting the select results of my CTE. I haven't been able to understand why this is, my original thinking was that within the CTE, I execute some SELECTstatement, then the ORDER BYshould work on THOSE results.
我在下面粘贴了我的 SQL 查询的一个非常简化的版本。我遇到的问题是该ORDER BY语句影响了我的 CTE 的选择结果。我一直无法理解为什么会这样，我最初的想法是在 CTE 中，我执行一些SELECT语句，然后ORDER BY应该对这些结果起作用。
Unfortunately the behavior that I'm seeing is that my inner SELECTstatement is being affected by the order by, giving me 'items' that are not in the TOP 10.
不幸的是，我看到的行为是我的内部SELECT语句受到 order by 的影响，给了我不在TOP 10.
Here is an example of data: (Indexed in reverse order by ID)
下面是一个数据示例：（按 ID 倒序索引）
ID, Date
9600 2010-10-12
9599 2010-09-08
9598 2010-08-31
9597 2010-08-31
9596 2010-08-30
9595 2010-08-11
9594 2010-08-06
9593 2010-08-05
9592 2010-08-02
....
9573 2010-08-10
....
8174 2010-08-05
....
38 2029-12-20
My basic query:
我的基本查询：
;with results as(
select TOP 10 ID, Date
from dbo.items
)
SELECT ID
FROM results
query returns:
查询返回：
ID, Date
9600 2010-10-12
9599 2010-09-08
9598 2010-08-31
9597 2010-08-31
9596 2010-08-30
9595 2010-08-11
9594 2010-08-06
9593 2010-08-05
9592 2010-08-02
My query with the ORDER BY
我的查询 ORDER BY
;with results as(
select TOP 10 ID, Date
from dbo.items
)
SELECT ID
FROM results
ORDER BY Date DESC
query returns:
查询返回：
ID, Date
38 2029-12-20
9600 2010-10-12
9599 2010-09-08
9598 2010-08-31
9597 2010-08-31
9596 2010-08-30
9595 2010-08-11
9573 2010-08-10
9594 2010-08-06
8174 2010-08-05
Can anyone explain why the first query will only return IDs that are in the top 10 of the table, and the second query returns the top 10 of the entire table (after the sorting is applied).
谁能解释为什么第一个查询将只返回表的前 10 个 ID，而第二个查询返回整个表的前 10 个（应用排序后）。
回答by Mark Byers
When you use SELECT TOP nyou mustsupply an ORDER BY if you want deterministic behaviour otherwise the server is free to return any 10 rows it feels like. The behaviour you are seeing is perfectly valid.
当您使用时SELECT TOP n，如果您想要确定性行为，则必须提供 ORDER BY，否则服务器可以自由地返回任何 10 行。您所看到的行为是完全有效的。
To solve the problem, specify an ORDER BY inside the CTE:
为了解决这个问题，在 CTE 中指定一个 ORDER BY：
WITH results AS
(
SELECT TOP 10 ID, Date
FROM dbo.items
ORDER BY ID DESC
)
SELECT ID
FROM results
ORDER BY Date
回答by Amit Dave
I think you can add new column like
我认为您可以添加新列，例如
SELECT ROW_NUMBER() OVER(ORDER BY <ColumnName>;) AS RowNo
and then all your columns.. this would help you to query using the CTE anchor... using between, where etc clauses..
然后是所有列.. 这将帮助您使用 CTE 锚点进行查询......使用 between, where etc 子句..
