没有 ORDER BY 的 Where 子句中的 SQL Row_Number() 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6390224/
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 Row_Number() function in Where Clause without ORDER BY?
提问by Legend
I found a bunch of questions on this topic with nice solutions but none of them actually deal with what to do if the data is not to be ordered in one specific way. For instance, the following query:
我发现了一堆关于这个主题的问题，有很好的解决方案，但没有一个真正涉及如果数据不以特定方式排序时该怎么做。例如，以下查询：
WITH MyCte AS
(
select employee_id,
RowNum = row_number() OVER ( order by employee_id )
from V_EMPLOYEE
ORDER BY Employee_ID
)
SELECT employee_id
FROM MyCte
WHERE RowNum > 0
works well if the data is to be ordered by employee_id. But what if my data does not have any specific order but the row numbers themselves act as an ID? My goal is to write a query like this (with the Row_Number()function having no ORDER BYclause):
如果要按employee_id 对数据进行排序，则效果很好。但是，如果我的数据没有任何特定顺序，但行号本身充当 ID 怎么办？我的目标是编写这样的查询（该Row_Number()函数没有ORDER BY子句）：
WITH MyCte AS
(
select employee_id,
RowNum = row_number() OVER ( <PRESERVE ORIGINAL ORDER FROM DB> )
from V_EMPLOYEE
ORDER BY Employee_ID
)
SELECT employee_id
FROM MyCte
WHERE RowNum > 0
EDIT: Upon Googling, I figured out that this is not really possible. Can some suggest a workaround for this?
编辑：通过谷歌搜索，我发现这不可能。有人可以为此提出解决方法吗？
回答by Legend
回答by kaborka
I use this to suppress the sort:
我用它来抑制排序：
ORDER BY @@rowcount
@@rowcount is constant within the query. Example:
@@rowcount 在查询中是常量。例子：
select N = row_number() over (order by @@rowcount) from sys.columns
Use of (select 0) in the ORDER BY seems to run much slower.
在 ORDER BY 中使用 (select 0) 似乎运行得慢得多。
回答by doug_w
The real problem with the approach that you are proposing is that order in the db is not guaranteed. It may coincidentally be returning to your application in the same order all of the time, but the SQL Standard guarantees no such order and may change depending on version or edition changes. The order of data from a SQL Server is not guaranteed without an order by clause. This design would be one that simply relies on 'luck.' If this possible variation in order has an impact on your implementation, you may want to change it now before you get too far into the implementation.
您提出的方法的真正问题是无法保证数据库中的顺序。它可能巧合地一直以相同的顺序返回到您的应用程序，但 SQL 标准保证没有这样的顺序，并且可能会根据版本或版本的变化而变化。如果没有 order by 子句，则无法保证来自 SQL Server 的数据顺序。这种设计将是一种完全依赖“运气”的设计。如果这种可能的顺序变化对您的实施产生影响，您可能希望在深入实施之前立即更改它。
回答by Tomas Kirda
There is no such thing as ORIGINAL ORDER. SQL server cannot guarantee order of rows if you don't specify ORDER BY. You may get lucky and get results in particular order, but it may change any time.
没有原始订单这样的东西。如果不指定 ORDER BY，SQL Server 无法保证行的顺序。您可能会很幸运并按特定顺序获得结果，但它可能随时更改。
回答by Zphunk
There's a solution that is simpler than above. You can still use ROW_NUMBER but supply an arbitrary value in the Order by clause:
有一个比上面更简单的解决方案。您仍然可以使用 ROW_NUMBER 但在 Order by 子句中提供任意值：
Select firstName, lastName, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) from tblPerson
