返回一个数字列表，达到设定值

2021-09-10 00:00:00 sql tsql sql-server

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

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

如果最大数量为 5，那么我假设上述回报:

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

rn
1
2
3
4
5

在 T-SQL 中是否有更优雅甚至更规范的方法来返回此数字列表?

Is there a more elegant, or even canonical, approach within T-SQL to return 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 来建立一组数字，然后交叉连接爆炸该组(你可以看到一个几个例子 这里，在 Inline 1/Inline 下2).

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

或者您可以构建一个数字表，假设您可能需要 5 个或您可能需要 100 万个:

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;

显然，使用 sys.all_columns 或任何具有足够行数的内置对象可以避免创建 Numbers 表的前期步骤(无论如何，出于某种原因，很多人都反对).

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

相关文章