什么是"ORDER BY(SELECT NULL)"?意思是? [英] What does "ORDER BY (SELECT NULL)" mean?

问题描述

以下SQL来自Itzik Ben-Gan，用于生成数字表. order by (select null)部分是什么意思?谢谢.

The following SQL is from Itzik Ben-Gan that is used to generate a numbers table. What does the order by (select null) part mean? Thanks.

DECLARE @number_of_numbers INT;
SELECT @number_of_numbers = 100000;

WITH    a AS ( SELECT   1 AS i
               UNION ALL
               SELECT   1
             ),
        b AS ( SELECT   1 AS i
               FROM     a AS x ,
                        a AS y
             ),
        c AS ( SELECT   1 AS i
               FROM     b AS x ,
                        b AS y
             ),
        d AS ( SELECT   1 AS i
               FROM     c AS x ,
                        c AS y
             ),
        e AS ( SELECT   1 AS i
               FROM     d AS x ,
                        d AS y
             ),
        f AS ( SELECT   1 AS i
               FROM     e AS x ,
                        e AS y
             ),
        numbers
          AS ( SELECT TOP ( @number_of_numbers )
                        ROW_NUMBER() OVER ( ORDER BY ( SELECT   NULL
                                                     ) ) AS number
               FROM     f
             )
    SELECT  *
    FROM    numbers;

谢谢！

推荐答案

ROW_NUMBER 在语法上需要ORDER BY子句.没有一个，您将无法使用它. SELECT NULL是一种在不强制执行任何特定命令的情况下关闭错误的方法.在这种情况下，我们不需要执行任何命令，因此最快的选择是使用SELECT NULL.

ROW_NUMBER requires an ORDER BY clause syntactically. You cannot use it without one. SELECT NULL is a hack to shut up the error while not enforcing any particular order. In this case we don't need to enforce any order, so the fastest option is to use SELECT NULL.

优化器可以解决这个问题，因此它没有运行时成本(通过查看执行计划可以轻松地验证此声明).

The optimizer sees through this trick, so it has no runtime cost (this claim is easily verified by looking at the execution plan).

这篇关于什么是"ORDER BY(SELECT NULL)"?意思是?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！