Pytanie SQL Wybierz rekordy "n" bez tabeli


Czy istnieje sposób wybierania określonej liczby wierszy bez tworzenia tabeli. na przykład jeśli użyję następującego:

SELECT 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

To da mi 10 w poprzek, chcę 10 nowych wierszy.

Dzięki


14
2018-06-30 10:40


pochodzenie


PostgreSQL ma funkcję GENERATE_SERIES (), która właśnie to robi. Nie wiem, które inne DB mogłyby zrobić coś podobnego. Prawdopodobnie można napisać procedurę przechowywaną, aby to zrobić, jeśli taka funkcja nie istnieje już w SQL Server. - Flimzy


Odpowiedzi:


Możesz użyć a rekurencyjne CTE generować dowolną sekwencję liczb w T-SQL, jak na przykład:

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

28
2018-06-30 10:46



+1 - Przekleństwa, które mnie pobiły! - Jon Egerton
Idealny! Dzięki - James
Realistycznie jednak, jeśli chcę mieć milion liczb, czy mogę ok. Milion razy? - Triynko
@Triynko: Nie zrobiłbym tego dla miliona numerów w kodzie produkcji. Spójrz na odpowiedź Vadima. - Dave Markle


Jeśli masz ustaloną liczbę wierszy, możesz spróbować:

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

18
2018-06-30 10:42



To też jest wspaniałe - dzięki! - James


Jest to dobry sposób, jeśli potrzebujesz długiej listy (więc nie potrzebujesz dużo UNIONsprawozdania:

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

7
2018-06-30 10:49



Jest ładny, ale ma maksymalny limit recusrion. - ypercubeᵀᴹ
Jeśli chcesz przekroczyć limit defautów, możesz użyć opcji, jak zrobił to Dave. Ograniczenie to wynosi 32767. Jest to wciąż limit, ale lepszy niż 32767 UNION Wszystkie instrukcje! ;-) - Jon Egerton
Tak, to prawda! Wypróbowałem Dave'a w SQL-Server Express 2005 i uruchomiłem go dla 100000 bez żadnego problemu. - ypercubeᵀᴹ


Podejście Recursive CTE - jest naprawdę dobre.

Bądź świadomy różnicy w wydajności. Zagrajmy z milionem rekordów:

Rekurencyjne podejście CTE. Czas trwania = 14 sekund

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

Podejście do Unii All + Cross Join. Czas trwania = 6 sekund

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;

Tabela Wartość Metoda Constructor + Cross Join. Czas trwania = 6 sekund

(jeśli 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;

Rekurencyjne podejście CTE + Cross Join. :) Czas trwania = 6 sekund

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;

Będziemy mieli niesamowity efekt, jeśli spróbujemy wstawić wynik do zmiennej tabeli:

INSERT INTO z rekurencyjnym podejściem CTE. Czas trwania = 17 sekund

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 z podejściem Cross Join. Czas trwania = 1 sekunda

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;

Oto ciekawy artykuł na temat Tally Tables


7
2017-12-28 11:03





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

4
2018-06-30 10:42





Używanie PIVOT (w niektórych przypadkach byłoby to przesadą)

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 

2
2018-06-30 10:53



bardzo kreatywny, uwielbiam to +1 - t-clausen.dk
@ t-clausen.dk, dzięki człowieku :) - sll


Używanie tabeli spt_values:

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

Lub jeśli wymagana wartość jest mniejsza niż 1k:

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

Jest to tabela używana przez wewnętrzne procedury przechowywane w różnych celach. Jego użycie w Internecie wydaje się dość powszechne, mimo że jest nieudokumentowane, nieobsługiwane, może zniknąć pewnego dnia, a ponieważ zawiera tylko skończony, nieunikalny i nieciągły zestaw wartości. W SQL Server 2008 R2 jest 2,164 unikatowych i 2508 wartości całkowitych; w 2012 r. jest ich 2 167 unikatowych i 2 515 ogółem. Obejmuje to duplikaty, wartości ujemne, a nawet jeśli korzystasz z DISTINCT, mnóstwo luk po przekroczeniu liczby 2.048. Tak więc obejście to należy użyć ROW_NUMBER() aby wygenerować ciągłą sekwencję, zaczynając od 1, w oparciu o wartości w tabeli.

Ponadto, aby wspomóc więcej wartości niż rekordy 2k, możesz dołączyć do samej tabeli, ale w zwykłych przypadkach sama ta tabela wystarcza.

Wydajność, nie powinno być tak źle (generowanie miliona rekordów zajęło 10 sekund na moim laptopie), a zapytanie jest dość łatwe do odczytania.

Źródło: http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1


1
2017-10-05 06:25





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

0
2017-12-28 07:09



Jakiś komentarz lub opis nie byłby taki zły ;-) - t3chb0t
Istnieją już dwie odpowiedzi pokazujące rekurencyjne CTE. Co dodaje twoja odpowiedź, co nie zostało jeszcze powiedziane? - Mikael Eriksson