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

发布时间：2020-12-12 08:32:54 所属栏目：MsSql教程来源：网络整理

导读：在一些其他数据库(例如DB2或具有ROWNUM的Oracle)中,我可以在排序函数的OVER()子句中省略ORDER BY子句.例如： ROW_NUMBER() OVER() 当与有序派生表一起使用时,这是特别有用的,例如： SELECT t.*,ROW_NUMBER() OVER()FROM ( SELECT ... ORDER BY) t 如何在SQL S

在一些其他数据库(例如DB2或具有ROWNUM的Oracle)中,我可以在排序函数的OVER()子句中省略ORDER BY子句.例如：

ROW_NUMBER() OVER()

当与有序派生表一起使用时,这是特别有用的,例如：

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

如何在SQL Server中仿效？我发现人们使用this trick,但是这是错误的,因为它将对派生表中的顺序具有非确定性：

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

一个具体的例子(从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)

另外,我不能使用派生表中的任何表达式来重现我的情况下的ORDER BY子句,因为派生表可能不可用,因为它可能由一些外部逻辑提供.

那我该怎么办呢我可以做吗

解决方法

Row_Number()OVER(ORDER BY(SELECT 1)))技巧不应被视为避免更改底层数据顺序的一种方式.只是避免使服务器执行额外的和不需要的排序(它可能仍然执行排序,但是与列排序相比,可能会花费最小的金额).

SQL Server中的所有查询绝对必须在最外层查询中具有ORDER BY子句,以便以有保证的方式可靠地排序结果.

关系数据库中不存在“保留原始顺序”的概念.始终将表和查询视为无序的,除非在最外层查询中指定了ORDER BY子句.

您可以尝试相同的无序查询100,000次,并始终以相同的顺序接收它,因此相信您可以依赖于所述排序.但这是一个错误,因为有一天会有变化,不会有你期望的顺序.一个例子是当数据库升级到新版本的SQL Server时 – 这引起了许多查询以更改其排序.但它不一定是那么大的变化.与添加或删除索引一样,可能会导致差异.还有更多：安装Service Pack.分隔一张桌子创建包含该表的索引视图.达到一个临时点,而不是寻找扫描.等等.

不要依赖结果来排序,除非你说“Server,ORDER BY”.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!