SQL SERVER Wspólne wyrażenie tabeli

Wspólne wyrażenie tabeli lub CTE to wymieniony zestaw wyników, który został wprowadzony w SQL Server 2005. Wspólne wyrażenie tabeli działa jako wirtualna tabela z rekordami i kolumnami, która jest tworzona podczas wykonywania zapytania z CTE i wydana po zakończeniu zapytania. Można go odwoływać do dowolnej instrukcji wyboru, wstawu, aktualizacji lub usuwania. Służy to również do stworzenia widoku.

CTE może być zdefiniowane przez składnię poniżej.

[Z [… ]]
Nazwa cte [(nazwa kolumn [,…])]
Jako (zapytanie CTE)
Wybierz * Z CTE

Przykład:

Z CTE_NAME (kolumna 1, kolumna2, kolumna3)
Jak
(
Wybierz kolumnę1, kolumna2, kolumna33
Z tabeli 1
Gdzie kolumna 1> 500
)

Zgodnie z przykładem, po zdefiniowaniu CTE CTE_Name, możemy użyć CTE natychmiast po zdefiniowaniu go jako tabeli. Poniżej jest przykład:

Wybierz z CTE_NAME

Zwróci wyjście trzech kolumn, kolumny1, kolumny2 i kolumny3.

Można go również używać w instrukcjach wstawienia, usuwania, aktualizacji i scalania. Pokazamy jeden przykład każdego poniżej.

Wiele CTE

Wiele CTE może być używane w jednym zapytaniu.

Z CTE_NAME1 (kolumna1, kolumna2, kolumna3)
Jak
(
Wybierz kolumnę1, kolumna2, kolumna33
Z tabeli 1
Gdzie kolumna 1> 100
)
JAK
(
Wybierz * z CTE_NAME2
gdzie kolumna 2> 200
)
Wybierz * z CTE_NAME2

Powyższe zapytanie zwróci rekordy z tabeli Tabela 1, w której kolumna 1 jest większa niż 100, a Kolumna 2 jest większa niż 200.

Usuń za pomocą CTE

CTE może być bardzo przydatne do usunięcia rekordów z tabeli.

Z CTE_NAME (kolumna 1, kolumna2, kolumna3)
Jak
(
Wybierz kolumnę1, kolumna2, kolumna33
Z tabeli 1
Gdzie kolumna 1> 100
)
Usuń z CTE_NAME

Powyższe oświadczenie usunie rekordy z tabeli podstawowej: Tabela Tabela 1, w której wartość kolumny 1 wynosi ponad 100.

Jest to również skuteczny sposób na wyeliminowanie zduplikowanych wpisów z tabeli. Poniżej jest przykład.

Z CTE_NAME (ID, kolumn1, kolumna2, kolumna3, rn)
Jak
(
Wybierz id, kolumn1, kolumna2, kolumna3, row_number () over (partycja według kolejności identyfikatora według identyfikatora) jako rn
Z tabeli 1
)
Usuń z CTE_NAME
Gdzie CTE_NAME. RN> 1

To usunie wszystkie zduplikowane wiersze z tabeli Tabela 1.

Wstaw za pomocą CTE

Możemy wstawić określony zestaw danych, który jest zdefiniowany w CTE do innej tabeli. Spójrz na poniższy przykład.

Z CTE_INSERT (id, kolumn1, kolumna2, kolumna3)
Jak
(
Wybierz id, kolumnę1, kolumna2, kolumna33
Z tabeli 1
Gdzie kolumna1> 200
)
/* W celu wprowadzenia w istniejącej tabeli dest_table*/
Wstaw do desty_table (kolumna 1, kolumna2, kolumna3)
Wybierz kolumnę1, kolumn2, kolumna3 z cte_insert
/ * Do utworzenia nowej tabeli dest_table_new i wstaw dane CTE */
Wybierz kolumnę1, kolumna2, kolumna33
Do dest_table_new

Powyższa instrukcja utworzy tabelę z trzema kolumnami- kolumna 1, kolumna2, kolumna3 i wstaw do niej dane.

Aktualizacja za pomocą CTE

Koncepcja aktualizacji za pomocą CTE jest taka sama, jak wstawianie i usuwanie. Sprawdźmy poniżej przykład.

Z CTE_UPDATE (id, kolumna1, kolumna2, kolumna3)
Jak
(
Wybierz id, kolumnę1, kolumna2, kolumna33
Z tabeli 1
Gdzie kolumna1> 200
)
/* Zaktualizuj tabelę podstawową- Tabela 1, CTE, aby zwiększyć wartość kolumny 1 przez 100*//
Zaktualizuj CTE_UPDATE
Ustaw kolumnę1 = kolumn1+100
/*Zaktualizuj inną tabelę - dest_table, używając wartości cte*/
Aktualizacja a
ustaw.kolumna1 = b.kolumna 1
z dest_table a
Dołącz CTE_UPDATE b
na.id = b.ID

Scal za pomocą CTE

Zapoznaj się z poniższym przykładem, aby uzyskać lepsze zrozumienie.

Z src_cte (id, kolumna1, kolumna2, kolumna3)
JAK
(
Wybierz id, kolumn1, kolumn2, kolumna33 z src_table
)
ŁĄCZYĆ
tgt_tbl jako cel
Używanie Src_Cte jako źródła
Na celu.id = źródło.ID)
Po dopasowaniu
UPDATE ZESTAW Target.Kolumna1 = Źródło.Kolumna 1,
cel.Kolumna2 = Źródło.Kolumna 2,
cel.Kolumna3 = źródło.Kolumna3
Gdy nie jest to dopasowane
Wartości wstawu (kolumna 1, kolumna2, kolumna33) (źródło.Kolumna 1, źródło.Kolumna2, źródło.Kolumna3);

W powyższym zapytaniu staramy się przyrostowo załadować dane z src_table do tgt_table.

Jak CTE, Tabela temperatury i zmienna temperatura są odroczone na serwerze SQL?

Z kilku ostatnich przykładów poznajemy zastosowania CTE i mamy jasne wyobrażenie o tym, co jest CTE. Teraz różnica między CTE IS a Tabelem Tempu i zmienną TEMP wynosi:

• • CTE zawsze potrzebuje pamięci, ale tabele tymczasowe potrzebują dysku. Zmienna tabeli używa obu. Nie powinniśmy więc używać CTE, gdy jest więcej danych danych.
  • Zakres zmiennej tabeli dotyczy tylko partii, a zakres tabeli tymczasowej dotyczy sesji, a zakres CTE dotyczy tylko zapytania.

Wniosek

CTE może przydać się, gdy trzeba wygenerować tymczasowy zestaw wyników i można go uzyskać w instrukcji wybierania, wstawu, aktualizacji, usuwania i scalania. Można go zoptymalizować pod względem zastosowań procesora i pamięci.