以下 SQL 来自 Itzik Ben-Gan,用于生成数字表。什么是order by (select null)
部分意思是?谢谢。
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;
Thanks!
ROW_NUMBER http://msdn.microsoft.com/en-us/library/ms186734.aspx需要一个ORDER BY
从句法上看。没有它你就无法使用它。SELECT NULL
是一种在不执行任何特定命令的情况下关闭错误的技巧。在这种情况下,我们不需要强制执行任何命令,因此最快的选择是使用SELECT NULL
.
优化器看穿了这个技巧,因此它没有运行时成本(这个说法很容易通过查看执行计划来验证)。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)