SELECT * FROM (SELECT *,ROW_NUMBER() OVER (Order by (select 1)) as rn ) as X where rn > 1000
查询工作正常.如果有任何方式我可以获得行没有不使用order by.
这里选择1是什么？
解决方法
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:
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
以上意味着当您使用常量排序时不会执行.我强烈建议您阅读本书,因为Itzik Ben-Gan深入介绍了窗口功能的工作原理以及如何在使用时优化各种情况.
