Pisanie zapytań korzystających z tabel pochodnych
Tabele pochodne umożliwiają pisanie instrukcji Języka Transact-SQL, które są bardziej modularne, co ułatwia podzielenie złożonych zapytań na bardziej zarządzane części. Użycie tabel pochodnych w zapytaniach może również zapewnić obejścia niektórych ograniczeń narzuconych przez logiczną kolejność przetwarzania zapytań, na przykład użycie aliasów kolumn.
Podobnie jak w przypadku podzapytania, należy utworzyć tabele pochodne w klauzuli FROM zewnętrznej instrukcji SELECT. W przeciwieństwie do podzapytań, można pisać tabele pochodne przy użyciu nazwanego wyrażenia, które jest logicznie równoważne tabeli i mogą być przywoływane jako tabela w innym miejscu zapytania zewnętrznego.
Tabele pochodne nie są przechowywane w bazie danych. W związku z tym żadne specjalne uprawnienia zabezpieczeń nie są wymagane do pisania zapytań przy użyciu tabel pochodnych, innych niż prawa do wyboru z obiektów źródłowych. Tabela pochodna jest tworzona w czasie wykonywania zapytania zewnętrznego i wychodzi poza zakres po zakończeniu zapytania zewnętrznego. Tabele pochodne nie muszą mieć wpływu na wydajność w porównaniu z tym samym zapytaniem wyrażonym inaczej. Po przetworzeniu zapytania instrukcja jest rozpakowana i oceniana względem bazowych obiektów bazy danych.
Zwracanie wyników przy użyciu tabel pochodnych
Aby utworzyć tabelę pochodną, należy napisać zapytanie wewnętrzne między nawiasami, a następnie klauzulę AS i nazwę tabeli pochodnej przy użyciu następującej składni:
SELECT <outer query column list>
FROM (SELECT <inner query column list>
FROM <table source>) AS <derived table alias>
Na przykład możesz użyć tabeli pochodnej, aby pobrać informacje o zamówieniach złożonych rocznie przez różnych klientów:
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders) AS derived_year
GROUP BY orderyear;
Zapytanie wewnętrzne tworzy zestaw zamówień i umieszcza go w pochodnym roku tabeli pochodnej. Zapytanie zewnętrzne działa w tabeli pochodnej i podsumowuje wyniki. Wyniki będą wyglądać następująco:
| orderyear | cust_count |
|---|---|
| 2019 | 67 |
| 2020 | 86 |
| 2021 | 81 |
Przekazywanie argumentów do tabel pochodnych
Tabele pochodne mogą akceptować argumenty przekazywane z procedury wywołującej, takiej jak partia Języka Transact-SQL, funkcja lub procedura składowana. Tabele pochodne można zapisywać przy użyciu zmiennych lokalnych, które pełnią rolę symboli zastępczych. W czasie wykonywania symbole zastępcze można zastąpić wartościami podanymi w partii lub wartościami przekazanymi jako parametry do procedury składowanej, która wywołała zapytanie. Pozwoli to na ponowne użycie kodu bardziej elastycznie niż ponowne zapisywanie tego samego zapytania przy użyciu różnych wartości za każdym razem.
Na przykład następująca partia deklaruje zmienną lokalną (oznaczoną symbolem @ ) dla identyfikatora pracownika, a następnie używa możliwości przypisania wartości do zmiennej w tej samej instrukcji. Zapytanie akceptuje zmienną @emp_id i używa jej w wyrażeniu tabeli pochodnej:
DECLARE @emp_id INT = 9; --declare and assign the variable
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
WHERE empid=@emp_id --use the variable to pass a value to the derived table query
) AS derived_year
GROUP BY orderyear;
GO
Podczas pisania zapytań korzystających z tabel pochodnych należy pamiętać o następujących wytycznych:
- Zagnieżdżona instrukcja SELECT definiująca tabelę pochodną musi mieć przypisany alias. Zapytanie zewnętrzne będzie używać aliasu w instrukcji SELECT w sposób podobny do tabel aliasów sprzężonych w klauzuli FROM.
- Wszystkie kolumny, do których odwołuje się klauzula SELECT tabeli pochodnej, powinny mieć przypisane aliasy— najlepsze rozwiązanie, które nie zawsze jest wymagane w języku Transact-SQL. Każdy alias musi być unikatowy w wyrażeniu. Aliasy kolumn mogą być zadeklarowane w tekście z kolumnami lub zewnętrznie do klauzuli .
- Instrukcja SELECT, która definiuje wyrażenie tabeli pochodnej, może nie używać klauzuli ORDER BY, chyba że zawiera również operator TOP, klauzulę OFFSET/FETCH lub klauzulę FOR XML. W związku z tym nie ma kolejności sortowania dostarczonej przez tabelę pochodną. Wyniki są sortowane w zapytaniu zewnętrznym.
- Instrukcja SELECT, która definiuje tabelę pochodną, może być zapisywana w celu akceptowania argumentów w postaci zmiennych lokalnych. Jeśli instrukcja SELECT jest osadzona w procedurze składowanej, argumenty mogą być zapisywane jako parametry procedury.
- Pochodne wyrażenia tabeli, które są zagnieżdżone w zapytaniu zewnętrznym, mogą zawierać inne pochodne wyrażenia tabeli. Zagnieżdżanie jest dozwolone, ale nie jest zalecane ze względu na zwiększoną złożoność i ograniczoną czytelność.
- Tabela pochodna może nie być odwoływanych wiele razy w zapytaniu zewnętrznym. Jeśli musisz manipulować tymi samymi wynikami, musisz zdefiniować wyrażenie tabeli pochodnej za każdym razem, na przykład po każdej stronie operatora JOIN.