SQL Выберите "n" записи без таблицы

Ответ 1

Вы можете использовать рекурсивный 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);

Ответ 2

Если у вас есть фиксированное количество строк, вы можете попробовать:

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

Это хороший способ, если вам нужен длинный список (так что вам не нужно много инструкций UNION:

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

Ответ 4

Рекурсивный подход CTE - это действительно хорошо.

Просто помните о разнице в производительности. Позвольте играть с миллионом записей:

Рекурсивный подход 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);

Соединение 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;

Конструктор Value Value + Cross Join. Длительность = 6 секунд

(если 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;

Рекурсивный подход CTE + Cross Join.:) Продолжительность = 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;

Мы получим более потрясающий эффект, если попытаемся получить результат INSERT в переменную таблицы:

INSERT INTO с рекурсивным подходом 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 с подходом 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;

Вот интересная статья о Таблицы таблиц

Ответ 5

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

Ответ 6

Использование 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 

Ответ 7

Использование таблицы spt_values:

SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY number) 
FROM [master]..spt_values ORDER BY n;

Или, если требуемое значение меньше 1k:

SELECT DISTINCT n = number FROM master..[spt_values] WHERE number BETWEEN 1 AND 1000;

Это таблица, которая используется внутренними хранимыми процедурами для различных целей. Его использование в Интернете кажется довольно распространенным, хотя оно недокументировано, неподдерживается, оно может исчезнуть в один прекрасный день и потому, что оно содержит только конечный, неповторимый и несмежный набор значений. В SQL Server 2008 R2 имеется 2164 уникальных и 2 508 общих значений; в 2012 году - 2167 уникальных и 2,515 всего. Это включает в себя дубликаты, отрицательные значения и даже при использовании DISTINCT, много пробелов, когда вы выходите за номер 2048. Поэтому обходным путем является использование ROW_NUMBER() для создания непрерывной последовательности, начиная с 1, на основе значений в таблице.

Кроме того, чтобы помочь больше значений, чем записи 2k, вы можете присоединиться к таблице с самим собой, но в общих случаях эта таблица сама по себе достаточно.

Производительность мудрая, это не должно быть слишком плохо (создание миллиона записей, это заняло 10 секунд на моем ноутбуке), и запрос довольно легко читается.

Источник: 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