SQL Select 'n' records without a Table

问题:

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.

Thanks

回答1:

You can use a recursive CTE to generate an arbitrary sequence of numbers in T-SQL like so:

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

回答2:

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

回答3:

This is a good way if you need a long list (so you don't need lots of UNIONstatements:

WITH CTE_Numbers AS (
    SELECT n = 1
    UNION ALL
    SELECT n + 1 FROM CTE_Numbers WHERE n < 10 
)
SELECT n FROM CTE_Numbers

回答4:

The Recursive CTE approach - is realy good.

Be just aware of performance difference. Let's play with a million of records:

Recursive CTE approach. Duration = 14 seconds

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

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

(if 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

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

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

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

回答5:

SELECT 1
UNION 
SELECT 2
UNION
...
UNION
SELECT 10 ;

回答6:

Using PIVOT (for some cases it would be overkill)

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 

回答7:

Using spt_values table:

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:

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.

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.

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.

Source: http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1

回答8:

;WITH nums AS
    (SELECT 1 AS value
    UNION ALL
    SELECT value + 1 AS value
    FROM nums
    WHERE nums.value <= 99)
SELECT *
FROM nums