SQL ROW_NUMBER 没有 ORDER BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44105691/
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
ROW_NUMBER Without ORDER BY
提问by lucy
I've to add row number in my existing query so that I can track how much data has been added into Redis. If my query failed so I can start from that row no which is updated in other table.
我必须在现有查询中添加行号，以便跟踪已添加到 Redis 中的数据量。如果我的查询失败，那么我可以从在其他表中更新的那一行开始。
Query to get data start after 1000 row from table
查询以从表中的 1000 行开始获取数据
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (Order by (select 1)) as rn ) as X where rn > 1000
Query is working fine. If any way that I can get the row no without using order by.
查询工作正常。如果我可以在不使用 order by 的情况下获得行号。
What is select 1here?
什么是select 1在这里吗？
Is the query optimized or I can do it by other ways. Please provide the better solution.
查询是优化的还是我可以通过其他方式来完成。请提供更好的解决方案。
回答by gotqn
There is no need to worry about specifying constant in the ORDER BYexpression. The following is quoted from the Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functionswritten by Itzik Ben-Gan(it was available for free download from Microsoft free e-books site):
无需担心在ORDER BY表达式中指定常量。以下引用自Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions编写的Itzik Ben-Gan（可从 Microsoft 免费电子书站点免费下载）：
As mentioned, a window order clause is mandatory, and SQL Server doesn't allow the ordering to be based on a constant—for example, ORDER BY NULL. But surprisingly, when passing an expression based on a subquery that returns a constant—for example, ORDER BY (SELECT NULL)—SQL Server will accept it. At the same time, the optimizer un-nests, or expands, the expression and realizes that the ordering is the same for all rows. Therefore, it removes the ordering requirement from the input data. Here's a complete query demonstrating this technique:
如前所述，窗口顺序子句是强制性的，并且 SQL Server 不允许基于常量的排序，例如 ORDER BY NULL。但令人惊讶的是，当传递基于返回常量的子查询的表达式时（例如 ORDER BY (SELECT NULL)），SQL Server 会接受它。同时，优化器取消嵌套或扩展表达式，并意识到所有行的顺序都相同。因此，它从输入数据中删除了排序要求。这是演示此技术的完整查询：
SELECT actid, tranid, val,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM dbo.Transactions;
Observe in the properties of the Index Scan iterator that the Ordered property is False, meaning that the iterator is not required to return the data in index key order
在Index Scan迭代器的属性中观察Ordered属性为False，意味着迭代器不需要按照索引键顺序返回数据
The above means that when you are using constant ordering is not performed. I will strongly recommend to read the book as Itzik Ben-Gandescribes in depth how the window functions are working and how to optimize various of cases when they are used.
以上意味着当您使用常量排序时不会执行。我强烈建议阅读本书，因为Itzik Ben-Gan它深入描述了窗口函数的工作原理以及如何在使用它们时优化各种情况。
回答by Damien_The_Unbeliever
Try just order by 1. Read the error message. Then reinstate the order by (select 1). Realise that whoever wrote this has, at some point, read the error message and then decided that the right thing to do is to trick the system into not raising an error rather than realising the fundamental truth that the error was trying to alert them to.
试试吧order by 1。阅读错误消息。然后恢复order by (select 1). 意识到写这篇文章的人在某个时候阅读了错误消息，然后决定正确的做法是欺骗系统不引发错误，而不是意识到错误试图提醒他们注意的基本事实。
Tables have no inherent order. If you want some form of ordering that you can rely upon, it's up to you to provide enough deterministic expression(s) to any ORDER BYclause such that each row is uniquely identified and ordered.
表没有固有的顺序。如果您想要某种可以依赖的排序形式，则由您为任何ORDER BY子句提供足够的确定性表达式，以便每一行都被唯一标识和排序。
Anything else, including tricking the system into not emitting errors, is hopingthat the system will do something sensible without using the tools provided to you to ensurethat it does something sensible - a well specified ORDER BYclause.
其他任何事情，包括诱使系统不发出错误，都是希望系统在不使用提供给您的工具的情况下做一些明智的事情来确保它做一些明智的事情 - 一个明确规定的ORDER BY条款。
回答by Madhivanan
You can use any literal value
您可以使用任何文字值
ex
前任
order by (select 0)
order by (select null)
order by (select 'test')
etc
等等
Refer this for more information https://exploresql.com/2017/03/31/row_number-function-with-no-specific-order/
有关更多信息，请参阅 https://exploresql.com/2017/03/31/row_number-function-with-no-specific-order/
