Good Mood

Podsumowanie: w tym samouczku dowiesz się o common table expression lub CTE w SQL Server za pomocą klauzuli WITH.

Wprowadzenie do CTE w SQL Server

CTE oznacza common table expression. CTE pozwala zdefiniować tymczasowy nazwany zestaw wyników, który jest dostępny tymczasowo w zakresie wykonywania instrukcji, takich jak SELECT, INSERT, UPDATE, DELETE lub MERGE.,

poniżej przedstawiono wspólną składnię CTE w SQL serverze:

Code language: SQL (Structured Query Language) (sql)

w tej składni:

• najpierw podaj nazwę wyrażenia (expression_name), do którego możesz się odnieść później w zapytaniu.
• następnie określ listę kolumn oddzielonych przecinkami po nazwie wyrażenia. Liczba kolumn musi być taka sama jak liczba kolumn zdefiniowana w CTE_definition.
• następnie użyj słowa kluczowego AS po nazwie wyrażenia lub liście kolumn, jeśli lista kolumn jest określona.,
• Po zdefiniowaniu instrukcji SELECT, której zestaw wyników wypełnia wyrażenie common table.
• na koniec należy odnieść się do wyrażenia wspólnej tabeli w zapytaniu (SQL_statement), takiego jak SELECT, INSERT, UPDATE, DELETE lub MERGE.

wolimy używać common table expressions zamiast subqueries, ponieważ common table expressions są bardziej czytelne., Używamy również CTE w zapytaniach zawierających funkcje analityczne (lub funkcje okien)

przykłady CTE SQL Server

weźmy kilka przykładów użycia wspólnych wyrażeń tabel.

A) prosty przykład CTE SQL Server

to zapytanie wykorzystuje CTE, aby zwrócić kwoty sprzedaży przez pracowników sprzedaży w 2018 roku:

poniższy obrazek pokazuje zestaw wyników:

w tym przykładzie:

• najpierw zdefiniowaliśmy cte_sales_amounts jako nazwę wyrażenia wspólnej tabeli., CTE zwraca wynik, który składa się z trzech kolumn staff,yearIsales wywodzących się z zapytania definicji.
• Po Drugie, stworzyliśmy zapytanie, które zwraca całkowitą kwotę sprzedaży według pracowników sprzedaży i roku, zapytując dane z tabel orders, order_items I staffs.
• Po Trzecie, odwołaliśmy się do CTE w zewnętrznym zapytaniu i wybieramy tylko wiersze, których Rok to 2018.,

zauważono, że ten przykład służy wyłącznie do celów demonstracyjnych, aby pomóc ci stopniowo zrozumieć, jak działają popularne wyrażenia tabelkowe. Istnieje bardziej optymalny sposób osiągnięcia wyniku bez użycia CTE.

B) UŻYWANIE wspólnego wyrażenia tabeli do tworzenia średnich raportów na podstawie zliczeń

Ten przykład wykorzystuje CTE, aby zwrócić średnią liczbę zleceń sprzedaży w 2018 r.dla wszystkich pracowników sprzedaży.

oto wyjście:

Code language: SQL (Structured Query Language) (sql)

w tym przykładzie:

najpierw użyliśmy cte_sales jako nazwy wyrażenia wspólnej tabeli., Pominęliśmy listę kolumn CTE, więc jest ona pochodną instrukcji definicji CTE. W tym przykładzie zawiera on kolumny staff_id I order_count.

Po Drugie, używamy następującego zapytania, aby zdefiniować zestaw wyników, który wypełnia wspólne wyrażenie tabeli cte_sales. Zapytanie zwraca liczbę zamówień w 2018 roku przez pracowników sprzedaży.

Po Trzecie, odnosimy się docte_sales w instrukcji zewnętrznej i używamy funkcji AVG(), aby uzyskać średnie zamówienie sprzedaży przez wszystkie sztaby.,

Code language: SQL (Structured Query Language) (sql)

C) używanie wielu serwerów SQL CTE w jednym zapytaniu przykład

poniższy przykład używa dwóch CTEcte_category_countsIcte_category_sales aby zwrócić liczbę produktów i sprzedaży dla każdej kategorii produktów. Zewnętrzne zapytanie łączy dwa CTE za pomocą kolumny category_id.,

oto zestaw wyników:

w tym samouczku nauczyłeś się używać popularnych wyrażeń tabel lub CTE w SQL Server do konstruowania złożonych zapytań w łatwy do zrozumienia sposób.