SQL CTE和ORDER BY影响结果集 [英] SQL CTE and ORDER BY affecting result set

问题描述

我在下面粘贴了一个非常简化的SQL查询版本。我遇到的问题是 ORDER BY 语句正在影响CTE的选择结果。我一直无法理解为什么会这样，我最初的想法是在CTE中执行一些 SELECT 语句，然后执行 ORDER BY 应该可以处理这些结果。

I've pasted a very simplified version of my SQL query below. The problem that I'm running into is that the ORDER BY statement 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 SELECT statement, then the ORDER BY should work on THOSE results.

不幸的是，我看到的行为是我的内部 SELECT 语句受顺序的影响，给我的不在 TOP 10 中。

Unfortunately the behavior that I'm seeing is that my inner SELECT statement is being affected by the order by, giving me 'items' that are not in the TOP 10.

以下是数据示例：
（按ID反向索引）

Here is an example of data: (Indexed in reverse order by 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

查询返回：

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

---

我的 ORDER BY

;with results as(
select TOP 10 ID, Date
from dbo.items
)
SELECT ID
FROM results
ORDER BY Date DESC

查询返回：

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

谁能解释为什么第一个查询只返回表前10位的ID，以及secon d查询返回整个表的前10位（应用排序后）。

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).

推荐答案

使用 SELECT TOP n 如果需要确定性行为，您必须提供ORDER BY，否则服务器可以自由返回感觉像的任何十行 。您看到的行为完全正确。

When you use SELECT TOP n you must supply 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.

要解决此问题，请在CTE中指定一个ORDER BY：

To solve the problem, specify an ORDER BY inside the CTE:

WITH results AS
(
    SELECT TOP 10 ID, Date
    FROM dbo.items
    ORDER BY ID DESC 
)
SELECT ID
FROM results
ORDER BY Date

这篇关于SQL CTE和ORDER BY影响结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！