SQL SQL选择没有表的'n'条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6533524/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Select 'n' records without a Table
提问by James
Is there a way of selecting a specific number of rows without creating a table. e.g. if i use the following:
有没有办法在不创建表的情况下选择特定数量的行。例如，如果我使用以下内容：
SELECT 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
It will give me 10 across, I want 10 New Rows.
它会给我 10 个，我想要 10 个新行。
Thanks
谢谢
回答by Dave Markle
You can use a recursive CTEto generate an arbitrary sequence of numbers in T-SQL like so:
您可以使用递归 CTE在 T-SQL 中生成任意数字序列，如下所示：
DECLARE @start INT = 1;
DECLARE @end INT = 10;
WITH numbers AS (
SELECT @start AS number
UNION ALL
SELECT number + 1
FROM numbers
WHERE number < @end
)
SELECT *
FROM numbers
OPTION (MAXRECURSION 0);
回答by CristiC
If you have a fixed number of rows, you can try:
如果您有固定数量的行，您可以尝试：
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6
UNION
SELECT 7
UNION
SELECT 8
UNION
SELECT 9
UNION
SELECT 10
回答by Vadim Loboda
The Recursive CTE approach - is realy good.
递归 CTE 方法 - 非常好。
Be just aware of performance difference. Let's play with a million of records:
请注意性能差异。让我们玩一百万条记录：
Recursive CTE approach. Duration = 14 seconds
递归 CTE 方法。持续时间 = 14 秒
declare @start int = 1;
declare @end int = 999999;
with numbers as
(
select @start as number
union all
select number + 1 from numbers where number < @end
)
select * from numbers option(maxrecursion 0);
Union All + Cross Join approach. Duration = 6 seconds
Union All + Cross Join 方法。持续时间 = 6 秒
with N(n) as
(
select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all select 1
)
select top 999999
row_number() over(order by (select 1)) as number
from
N n1, N n2, N n3, N n4, N n5, N n6;
Table Value Constructor + Cross Join approach. Duration = 6 seconds
表值构造函数 + 交叉连接方法。持续时间 = 6 秒
(if SQL Server >= 2008)
（如果 SQL Server >= 2008）
with N as
(
select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t(n)
)
select top 999999
row_number() over(order by (select 1)) as number
from
N n1, N n2, N n3, N n4, N n5, N n6;
Recursive CTE + Cross Join approach. :) Duration = 6 seconds
递归 CTE + 交叉连接方法。:) 持续时间 = 6 秒
with N(n) as
(
select 1
union all
select n + 1 from N where n < 10
)
select top 999999
row_number() over(order by (select 1)) as number
from
N n1, N n2, N n3, N n4, N n5, N n6;
We will get more amazing effect if we try to INSERT result into a table variable:
如果我们尝试将结果插入到表变量中，我们会得到更惊人的效果：
INSERT INTO with Recursive CTE approach. Duration = 17 seconds
使用递归 CTE 方法插入。持续时间 = 17 秒
declare @R table (Id int primary key clustered);
with numbers as
(
select 1 as number
union all
select number + 1 from numbers where number < 999999
)
insert into @R
select * from numbers option(maxrecursion 0);
INSERT INTO with Cross Join approach. Duration = 1 second
INSERT INTO 与 Cross Join 方法。持续时间 = 1 秒
declare @C table (Id int primary key clustered);
with N as
(
select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t(n)
)
insert into @C
select top 999999
row_number() over(order by (select 1)) as number
from
N n1, N n2, N n3, N n4, N n5, N n6;
Here is an interesting article about Tally Tables
这是一篇关于Tally Tables的有趣文章
回答by Jon Egerton
This is a good way if you need a long list (so you don't need lots of UNIONstatements:
如果您需要一个长列表，这是一个好方法（因此您不需要很多UNION语句：
WITH CTE_Numbers AS (
SELECT n = 1
UNION ALL
SELECT n + 1 FROM CTE_Numbers WHERE n < 10
)
SELECT n FROM CTE_Numbers
回答by ypercube??
SELECT 1
UNION
SELECT 2
UNION
...
UNION
SELECT 10 ;
回答by Syakur Rahman
Using spt_values table:
使用 spt_values 表：
SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY number)
FROM [master]..spt_values ORDER BY n;
Or if the value needed is less than 1k:
或者如果需要的值小于 1k：
SELECT DISTINCT n = number FROM master..[spt_values] WHERE number BETWEEN 1 AND 1000;
This is a table that is used by internal stored procedures for various purposes. Its use online seems to be quite prevalent, even though it is undocumented, unsupported, it may disappear one day, and because it only contains a finite, non-unique, and non-contiguous set of values. There are 2,164 unique and 2,508 total values in SQL Server 2008 R2; in 2012 there are 2,167 unique and 2,515 total. This includes duplicates, negative values, and even if using DISTINCT, plenty of gaps once you get beyond the number 2,048. So the workaround is to use
ROW_NUMBER()to generate a contiguous sequence, starting at 1, based on the values in the table.
这是一个由内部存储过程用于各种目的的表。它在网上的使用似乎相当普遍，即使它没有记录、不受支持，它也可能有一天会消失，因为它只包含一组有限的、非唯一的和非连续的值。SQL Server 2008 R2 中有 2,164 个唯一值和 2,508 个总值；2012 年有 2,167 个独特的，总共 2,515 个。这包括重复项、负值，即使使用 DISTINCT，一旦超过数字 2,048，就会出现大量空白。因此，解决方法是使用
ROW_NUMBER()基于表中的值生成从 1 开始的连续序列。
In addition, to aid more values than 2k records, you could join the table with itself, but in common cases, that table itself is enough.
此外，为了帮助比 2k 记录更多的值，您可以将表与自身连接，但在通常情况下，该表本身就足够了。
Performance wise, it shouldn't be too bad (generating a million records, it took 10 seconds on my laptop), and the query is quite easy to read.
性能方面，它应该不会太糟糕（生成一百万条记录，在我的笔记本电脑上花了 10 秒），并且查询很容易阅读。
Source: http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
来源：http: //sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
回答by sll
Using PIVOT (for some cases it would be overkill)
使用 PIVOT（在某些情况下它会矫枉过正）
DECLARE @Items TABLE(a int, b int, c int, d int, e int);
INSERT INTO @Items
VALUES(1, 2, 3, 4, 5)
SELECT Items
FROM @Items as p
UNPIVOT
(Items FOR Seq IN
([a], [b], [c], [d], [e]) ) AS unpvt
回答by Jangli Coder
;WITH nums AS
(SELECT 1 AS value
UNION ALL
SELECT value + 1 AS value
FROM nums
WHERE nums.value <= 99)
SELECT *
FROM nums
