计算派生表的SQL Server ROW_NUMBER() / ()

In some other databases (e.g. DB2, or Oracle with ROWNUM), I can omit the ORDER BY clause in a ranking function's OVER() clause. For instance:

在其他一些数据库中(例如DB2或带ROWNUM的Oracle)，我可以省略排序函数的OVER()子句中的ORDER BY子句。例如:

ROW_NUMBER() OVER()

This is particularly useful when used with ordered derived tables, such as:

当使用有序派生表时，这一点特别有用，例如:

SELECT t.*, ROW_NUMBER() OVER()
FROM (
    SELECT ...
    ORDER BY
) t

How can this be emulated in SQL Server? I've found people using this trick, but that's wrong, as it will behave non-deterministically with respect to the order from the derived table:

如何在SQL Server中进行模拟?我发现人们使用这个技巧，但这是错误的，因为它对来自派生表的顺序是不确定的:

-- This order here ---------------------vvvvvvvv
SELECT t.*, ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM (
    SELECT TOP 100 PERCENT ...
    -- vvvvv ----redefines this order here
    ORDER BY
) t

A concrete example (as can be seen on SQLFiddle):

一个具体的例子(如SQLFiddle所示):

SELECT v, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM (
  SELECT TOP 100 PERCENT 1 UNION ALL
  SELECT TOP 100 PERCENT 2 UNION ALL
  SELECT TOP 100 PERCENT 3 UNION ALL
  SELECT TOP 100 PERCENT 4
  -- This descending order is not maintained in the outer query
  ORDER BY 1 DESC
) t(v)

Also, I cannot reuse any expression from the derived table to reproduce the ORDER BY clause in my case, as the derived table might not be available as it may be provided by some external logic.

而且，我不能重用派生表中的任何表达式来在我的情况下按照子句重新生成ORDER BY子句，因为派生表可能无法提供，因为它可能是由一些外部逻辑提供的。

So how can I do it? Can I do it at all?

那我该怎么做呢?我能做吗?

1 个解决方案

#1

The Row_Number() OVER (ORDER BY (SELECT 1)) trick should NOT be seen as a way to avoid changing the order of underlying data. It is only a means to avoid causing the server to perform an additional and unneeded sort (it may still perform the sort but it's going to cost the minimum amount possible when compared to sorting by a column).

不应该将Row_Number() / (ORDER BY (SELECT 1))技巧视为避免更改底层数据顺序的方法。这只是一种避免导致服务器执行额外且不需要的排序的方法(它可能仍然执行排序，但与按列排序相比，它将花费尽可能少的开销)。

All queries in SQL server ABSOLUTELY MUST have an ORDER BY clause in the outermost query for the results to be reliably ordered in a guaranteed way.

SQL server中的所有查询必须在最外面的查询中具有ORDER BY子句，以便以一种有保证的方式可靠地排序结果。

The concept of "retaining original order" does not exist in relational databases. Tables and queries must always be considered unordered until and unless an ORDER BY clause is specified in the outermost query.

关系数据库中不存在“保持原始顺序”的概念。除非在最外层的查询中指定了ORDER BY子句，否则必须始终认为表和查询是无序的。

You could try the same unordered query 100,000 times and always receive it with the same ordering, and thus come to believe you can rely on said ordering. But that would be a mistake, because one day, something will change and it will not have the order you expect. One example is when a database is upgraded to a new version of SQL Server--this has caused many a query to change its ordering. But it doesn't have to be that big a change. Something as little as adding or removing an index can cause differences. And more: Installing a service pack. Partitioning a table. Creating an indexed view that includes the table in question. Reaching some tipping point where a scan is chosen instead of a seek. And so on.

您可以尝试相同的无序查询100,000次，并始终以相同的顺序接收它，因此相信您可以依赖于该无序查询。但那将是一个错误，因为有一天，某些东西会改变，它不会有你所期望的顺序。一个例子是，当数据库升级到新的SQL Server版本时——这会导致许多查询更改其排序。但这并不一定是很大的改变。只要添加或删除一个索引，就会引起差异。更多:安装服务包。分区表。创建一个包含问题表的索引视图。达到某个临界点，选择扫描而不是寻找。等等。

Do not rely on results to be ordered unless you have said "Server, ORDER BY".

不要依赖要排序的结果，除非您说过“Server, ORDER BY”。

#1