Kiedy stosować Common Table Expression (CTE)


230

Zacząłem czytać o Common Table Expression i nie mogę wymyślić przypadku użycia, w którym musiałbym ich użyć. Wydają się być zbędne, podobnie jak w przypadku tabel pochodnych. Czy brakuje mi czegoś lub źle rozumiem? Czy ktoś może podać prosty przykład ograniczeń z regularnymi zapytaniami do tabeli wyboru, pochodnej lub tabeli tymczasowej, aby przedstawić przypadek CTE? Wszelkie proste przykłady byłyby bardzo mile widziane.

Odpowiedzi:


197

Jeden przykład, jeśli trzeba wielokrotnie odwoływać się do tego samego zestawu danych / dołączyć go, można to zrobić, definiując CTE. Dlatego może to być forma ponownego użycia kodu.

Przykładem samoreferencji jest rekurencja: zapytania rekurencyjne z wykorzystaniem CTE

Ekscytujące definicje Microsoft zaczerpnięte z Books Online:

CTE można wykorzystać do:

  • Utwórz zapytanie rekurencyjne. Aby uzyskać więcej informacji, zobacz Zapytania rekurencyjne przy użyciu typowych wyrażeń tabelowych.

  • Zastępuje widok, gdy ogólne użycie widoku nie jest wymagane; to znaczy, nie musisz przechowywać definicji w metadanych.

  • Włącz grupowanie według kolumny pochodzącej z podselekcji skalarnej lub funkcji, która nie jest deterministyczna lub ma dostęp zewnętrzny.

  • Odwołaj się do tabeli wynikowej wiele razy w tej samej instrukcji.


7
Tak. Nie możesz samodzielnie dołączyć do tabeli pochodnej. Warto jednak podkreślić, że samodzielne dołączenie do CTE nadal pozostawia ci 2 osobne wywołania tego.
Martin Smith

@Martin - jestem zaskoczony. Czy możesz wykonać kopię zapasową tego oświadczenia?
RichardTheKiwi

@John Dzięki, znajduję 4guysfromrolla.com/webtech/071906-1.shtml też całkiem przydatny
imak

4
@cyberkiwi - Który bit? Że samodzielne dołączenie doprowadzi do 2 różnych wywołań? Zobacz przykład w tej odpowiedzi stackoverflow.com/questions/3362043/…
Martin Smith

4
Ciekawy fakt na temat CTE. Zawsze zastanawiałem się, dlaczego NEWID () w CTE zmienia się, gdy do CTE odwołuje się więcej niż jeden raz. select top 100 * into #tmp from master..spt_values order by 1,2,3,4 select A.number, COUNT(*) from #tmp A inner join #tmp B ON A.number = B.number+1 group by A.numbervswith CTE AS (select top 100 * from master..spt_values order by 1,2,3,4) select A.number, COUNT(*) from CTE A inner join CTE B ON A.number = B.number+1 group by A.number
RichardTheKiwi

50

Używam ich do dzielenia złożonych zapytań, szczególnie złożonych sprzężeń i podpytw zapytań. Uważam, że coraz częściej używam ich jako „pseudo-widoków”, aby pomóc mi zrozumieć cel zapytania.

Moją jedyną skargą na nie jest to, że nie można ich ponownie użyć. Na przykład mogę mieć zapisany proc z dwiema instrukcjami aktualizacji, które mogłyby korzystać z tego samego CTE. Ale „zakres” CTE jest tylko pierwszym zapytaniem.

Problem w tym, że „proste przykłady” prawdopodobnie tak naprawdę nie potrzebują CTE!

Mimo to bardzo przydatny.


dobrze. Czy potrafisz uzasadnić jakiś stosunkowo skomplikowany przykład, który może pomóc w mojej głowie wokół tej koncepcji?
imak

28
„Moją jedyną skargą na nie jest to, że nie można ich ponownie użyć” - CTE, które chcesz ponownie wykorzystać, należy uznać za kandydata na VIEW:)
dniu

6
@onedaywhen: Zrozumiałem, ale to oznacza zasięg globalny, z którym nie zawsze czuję się komfortowo. Czasami w ramach proc. Chciałbym zdefiniować CTE i użyć go do selekcji i aktualizacji lub selekcji podobnych danych z różnych tabel.
n8wrl

5
Kiedy potrzebuję tego samego CTE więcej niż raz, podaję go do tabeli tymczasowej, a następnie używam tabeli tymczasowej, ile chcę.
Fandango68

43

Są dwa powody, dla których używam cte.

Aby użyć wartości obliczonej w klauzuli where. Wydaje mi się to trochę czystsze niż tabela pochodna.

Załóżmy, że istnieją dwie tabele - Pytania i odpowiedzi połączone razem przez pytania.ID = Answers.Question_Id (i identyfikator quizu)

WITH CTE AS
(
    Select Question_Text,
           (SELECT Count(*) FROM Answers A WHERE A.Question_ID = Q.ID) AS Number_Of_Answers
    FROM Questions Q
)
SELECT * FROM CTE
WHERE Number_Of_Answers > 0

Oto kolejny przykład, w którym chcę uzyskać listę pytań i odpowiedzi. Chcę, aby odpowiedzi były grupowane z pytaniami w wynikach.

WITH cte AS
(
    SELECT [Quiz_ID] 
      ,[ID] AS Question_Id
      ,null AS Answer_Id
          ,[Question_Text]
          ,null AS Answer
          ,1 AS Is_Question
    FROM [Questions]

    UNION ALL

    SELECT Q.[Quiz_ID]
      ,[Question_ID]
      ,A.[ID] AS  Answer_Id
      ,Q.Question_Text
          ,[Answer]
          ,0 AS Is_Question
        FROM [Answers] A INNER JOIN [Questions] Q ON Q.Quiz_ID = A.Quiz_ID AND Q.Id = A.Question_Id
)
SELECT 
    Quiz_Id,
    Question_Id,
    Is_Question,
    (CASE WHEN Answer IS NULL THEN Question_Text ELSE Answer END) as Name
FROM cte    
GROUP BY Quiz_Id, Question_Id, Answer_id, Question_Text, Answer, Is_Question 
order by Quiz_Id, Question_Id, Is_Question Desc, Name

10
Czy nie można uprościć pierwszego przykładu, aby po prostu użyć zapytania zagnieżdżonego zamiast CTE?
Sam

2
Oba przykłady mogą być.
Manachi

3
Powinieneś dodać pierwszy bez CTE, od razu widać, dlaczego jest on przydatny.
Ufos,

HAVINGjest innym sposobem na wykonanie filtra późnego etapu, który może być podobny do korzystania z sub-SELECT
William Entriken

21

Jednym ze scenariuszy, który uznałem za użyteczny przy użyciu CTE, jest to, gdy chcesz uzyskać DISTINCT wiersze danych na podstawie jednej lub więcej kolumn, ale zwrócisz wszystkie kolumny w tabeli. W przypadku standardowego zapytania może być konieczne zrzucenie różnych wartości do tabeli tymczasowej, a następnie próba ponownego połączenia ich z oryginalną tabelą w celu odzyskania pozostałych kolumn lub napisania bardzo złożonego zapytania dotyczącego partycji, które może zwrócić wyniki jeden przebieg, ale najprawdopodobniej będzie nieczytelny i spowoduje problemy z wydajnością.

Ale za pomocą CTE (jak odpowiedział Tim Schmelter na Select the first instance of a record )

WITH CTE AS(
    SELECT myTable.*
    , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
    FROM myTable 
)
SELECT * FROM CTE
WHERE RN = 1

Jak widać, jest to o wiele łatwiejsze do odczytania i utrzymania. I w porównaniu do innych zapytań jest znacznie lepszy w wydajności.


16

Być może bardziej sensowne jest myślenie o CTE jako zamienniku widoku używanego dla pojedynczego zapytania. Ale nie wymaga narzutu, metadanych ani trwałości formalnego widoku. Bardzo przydatne, gdy potrzebujesz:

  • Utwórz zapytanie rekurencyjne.
  • Użyj zestawu wyników CTE więcej niż raz w zapytaniu.
  • Promuj przejrzystość zapytania, redukując duże fragmenty identycznych podzapytań.
  • Włącz grupowanie według kolumny pochodzącej z zestawu wyników CTE

Oto przykład wycinania i wklejania do zabawy:

WITH [cte_example] AS (
SELECT 1 AS [myNum], 'a num' as [label]
UNION ALL
SELECT [myNum]+1,[label]
FROM [cte_example]
WHERE [myNum] <=  10
)
SELECT * FROM [cte_example]
UNION
SELECT SUM([myNum]), 'sum_all' FROM [cte_example]
UNION
SELECT SUM([myNum]), 'sum_odd' FROM [cte_example] WHERE [myNum] % 2 = 1
UNION
SELECT SUM([myNum]), 'sum_even' FROM [cte_example] WHERE [myNum] % 2 = 0;

Cieszyć się


7

Dzisiaj dowiemy się o Wyrażeniu wspólnej tabeli, która jest nową funkcją, która została wprowadzona w SQL Server 2005 i dostępna również w późniejszych wersjach.

Wspólne wyrażenie tabeli: - Wspólne wyrażenie tabeli można zdefiniować jako tymczasowy zestaw wyników lub innymi słowy jest to zamiennik widoków w programie SQL Server. Wspólne wyrażenie tabelowe jest poprawne tylko w partii instrukcji, w której zostało zdefiniowane, i nie może być używane w innych sesjach.

Składnia deklarującego CTE (wspólne wyrażenie tabelowe): -

with [Name of CTE]
as
(
Body of common table expression
)

Weźmy przykład: -

CREATE TABLE Employee([EID] [int] IDENTITY(10,5) NOT NULL,[Name] [varchar](50) NULL)

insert into Employee(Name) values('Neeraj')
insert into Employee(Name) values('dheeraj')
insert into Employee(Name) values('shayam')
insert into Employee(Name) values('vikas')
insert into Employee(Name) values('raj')

CREATE TABLE DEPT(EID INT,DEPTNAME VARCHAR(100))
insert into dept values(10,'IT')
insert into dept values(15,'Finance')
insert into dept values(20,'Admin')
insert into dept values(25,'HR')
insert into dept values(10,'Payroll')

Utworzyłem dwie tabele pracownik i Dział i wstawiłem 5 wierszy w każdej tabeli. Teraz chciałbym dołączyć do tych tabel i utworzyć tymczasowy zestaw wyników, aby dalej z niego korzystać.

With CTE_Example(EID,Name,DeptName)
as
(
select Employee.EID,Name,DeptName from Employee 
inner join DEPT on Employee.EID =DEPT.EID
)
select * from CTE_Example

Weźmy każdy wiersz instrukcji jeden po drugim i zrozummy.

Aby zdefiniować CTE, piszemy klauzulę „with”, a następnie nadajemy nazwę wyrażeniu tabelowemu, tutaj nadałem nazwę jako „CTE_Example”

Następnie piszemy „As” i umieszczamy nasz kod w dwóch nawiasach (---), możemy łączyć wiele tabel w załączonych nawiasach.

W ostatnim wierszu użyłem „Wybierz * z CTE_Example”, odnoszimy się do wyrażenia Common table w ostatnim wierszu kodu, więc możemy powiedzieć, że jest to widok, w którym definiujemy i używamy widoku w jednym partia i CTE nie są przechowywane w bazie danych jako obiekt stały. Ale zachowuje się jak widok. możemy wykonać usunięcie i aktualizację instrukcji CTE, co będzie miało bezpośredni wpływ na tabelę, do której się odnoszą, które są używane w CTE. Weźmy przykład, aby zrozumieć ten fakt.

With CTE_Example(EID,DeptName)
as
(
select EID,DeptName from DEPT 
)
delete from CTE_Example where EID=10 and DeptName ='Payroll'

W powyższej instrukcji usuwamy wiersz z CTE_Example i spowoduje to usunięcie danych z przywoływanej tabeli „DEPT”, która jest używana w CTE.


Nadal nie rozumiem. Jaka jest różnica między tym a usunięciem z DEPT z dokładnie tym samym warunkiem? Wydaje się, że to nic nie ułatwia.
Holger Jakobs

Popraw mnie, jeśli się mylę, ale plan wykonania może być inny i myślę, że Neeraj ma na myśli to, że istnieje wiele sposobów na osiągnięcie tego samego celu, ale niektóre będą miały przewagę nad innymi w zależności od sytuacji. Na przykład w niektórych okolicznościach może być łatwiej odczytać CTE zamiast instrukcji DELETE FROM, w innych może być też odwrotnie. Wydajność może ulec poprawie lub pogorszeniu. itp.
WonderWorker,

7

Jest to bardzo przydatne, gdy chcesz wykonać „zamówioną aktualizację”.

MS SQL nie pozwala na użycie ORDER BY z UPDATE, ale przy pomocy CTE możesz to zrobić w ten sposób:

WITH cte AS
(
    SELECT TOP(5000) message_compressed, message, exception_compressed, exception
    FROM logs
    WHERE Id >= 5519694 
    ORDER BY Id
)
UPDATE  cte
SET     message_compressed = COMPRESS(message), exception_compressed = COMPRESS(exception)

Zajrzyj tutaj, aby uzyskać więcej informacji: Jak zaktualizować i zamówić za pomocą ms sql


0

Jedną z kwestii, których jeszcze nie wymieniono, jest prędkość . Wiem, że to stare pytanie, na które udzielono odpowiedzi, ale myślę, że zasługuje na bezpośredni komentarz / odpowiedź:

Wydają się być zbędne, podobnie jak w przypadku tabel pochodnych

Kiedy pierwszy raz użyłem CTE, byłem absolutnie oszołomiony jego szybkością. To był przypadek jak z podręcznika, bardzo odpowiedni dla CTE, ale we wszystkich przypadkach, w których kiedykolwiek korzystałem z CTE, nastąpił znaczny wzrost prędkości. Moje pierwsze zapytanie było złożone z tabelami pochodnymi, których wykonanie zajęło wiele minut. Z CTE zajęło to ułamki sekund i wstrząsnęło mną, że to w ogóle możliwe.


-4
 ;with cte as
  (
  Select Department, Max(salary) as MaxSalary
  from test
  group by department
  )  
  select t.* from test t join cte c on c.department=t.department 
  where t.salary=c.MaxSalary;

Spróbuj tego

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.