問題描述
我已經(jīng)看到以下用于返回?cái)?shù)字列表
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
如果最大數(shù)量為 5，那么我假設(shè)上述回報:
if the max quantity is 5 then I assume the above returns:
rn
1
2
3
4
5
在 T-SQL 中是否有更優(yōu)雅甚至更規(guī)范的方法來返回此數(shù)字列表?
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;
當(dāng)數(shù)字是 5 時，這是可以容忍的，但不是 50 或 5000.當(dāng)你需要更多時，你可以做一些事情，比如使用 CTE 來建立一組數(shù)字，然后交叉連接爆炸該組(你可以看到一個幾個例子 這里，在 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).
或者您可以構(gòu)建一個數(shù)字表，假設(shè)您可能需要 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
然后當(dāng)你想要一些數(shù)字時，你只需說:
Then when you want some numbers you just say:
SELECT TOP (5) rn = Number
FROM dbo.Numbers
ORDER BY Number;
顯然，使用 sys.all_columns 或任何具有足夠行數(shù)的內(nèi)置對象可以避免創(chuàng)建 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).
現(xiàn)在，如果有一種更優(yōu)雅的方式來做到這一點(diǎn)，那就太好了，不是嗎?您不會在任何當(dāng)前版本中看到它，但我們有可能在未來版本中看到它.請?jiān)诖颂幫镀?更重要的是，對您的用例發(fā)表評論):
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
這篇關(guān)于返回一個數(shù)字列表，達(dá)到設(shè)定值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！