問題描述
我已經看到以下用于返回數字列表
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模板網！