返回最多为设定值的数字列表(Returning a list of numbers up to a set value)

返回最多为设定值的数字列表(Returning a list of numbers up to a set value)

我已经看到以下用于返回数字列表

SELECT TOP (SELECT MAX(Quantity) FROM @d) rn = ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.all_columns ORDER BY object_id

如果最大数量是5，那么我假设上面的回报：

rn 1 2 3 4 5

是否有更优雅的方式返回这个数字列表？

I've seen the following used to return a list of numbers

SELECT TOP (SELECT MAX(Quantity) FROM @d) rn = ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.all_columns ORDER BY object_id

if the max quantity is 5 then I assume the above returns:

rn 1 2 3 4 5

Is there a more elegant way of returning this list of numbers?

最满意答案

你可以做：

SELECT rn = 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5;

当数字为5但不是50或5000时，这是可以容忍的。当你需要更多时，你可以做一些事情，比如使用CTE建立一组数字然后交叉连接来爆炸集合（你可以看到几个例子） 在这里，在内联1 /内联2 ）。

或者你可以建立一个数字表，假设你可能需要5或者你可能需要一百万：

SET NOCOUNT ON; DECLARE @UpperLimit INT = 1000000; WITH n AS ( SELECT x = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 CROSS JOIN sys.all_objects AS s3 ) SELECT Number = x INTO dbo.Numbers FROM n WHERE x BETWEEN 1 AND @UpperLimit; GO CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers(Number); GO

然后，当你想要一些数字时，你只需说：

SELECT TOP (5) rn = Number FROM dbo.Numbers ORDER BY Number;

显然，使用sys.all_columns或任何具有足够行的内置对象可以避免创建Numbers表的前期步骤（无论如何，许多人都会反对这些表）。

现在，如果有更优雅的方式来做这件事真的很好，不是吗？ 你不会在任何当前版本中看到它，但我们有可能在未来的版本中看到它。 请在这里投票（更重要的是，评论您的用例）：

http://connect.microsoft.com/SQLServer/feedback/details/258733/add-a-built-in-table-of-numbers

You can do:

SELECT rn = 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5;

This is tolerable when the number is 5, but not 50 or 5000. When you need more you can do things like use a CTE to build up a set of numbers to then cross join to explode the set (you can see a couple of examples here, under Inline 1 / Inline 2).

Or you can build a table of Numbers, let's say you may need 5 or you may need a million:

SET NOCOUNT ON; DECLARE @UpperLimit INT = 1000000; WITH n AS ( SELECT x = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 CROSS JOIN sys.all_objects AS s3 ) SELECT Number = x INTO dbo.Numbers FROM n WHERE x BETWEEN 1 AND @UpperLimit; GO CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers(Number); GO

Then when you want some numbers you just say:

SELECT TOP (5) rn = Number FROM dbo.Numbers ORDER BY Number;

Obviously using sys.all_columns or any built-in object with sufficient rows avoids the up-front step of creating a Numbers table (which many people object to, for some reason, anyway).

Now, it would be really nice if there were a more elegant way to do this, wouldn't it? You won't see it in any current version but there's a chance we'll see it in a future version. Please go vote (and more importantly, comment on your use case) here:

http://connect.microsoft.com/SQLServer/feedback/details/258733/add-a-built-in-table-of-numbers