問題描述

我已經看到以下用于返回數字列表

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

這篇關于返回一個數字列表，達到設定值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！