T-SQL bez tajemnic cz. 4
20.12.17r.
Rekurencyjne wyrażeia CTE, które umożliwiają efektywne przeszukiwanie hierarchii danych. Przedstawiamy praktyczne zastosowanie rekurencji w zapytaniach SQL na przykładzie tabeli Employees.
W poprzednim artykule „
T-SQL BEZ TAJEMNIC CZ. 3” skupiliśmy sią na logice działania nierekurencyjnych wyrażeń
CTE. Zobaczyliśmy na przykładach jak bardzo pomocne mogą być wyrażenia common table expression między innymi przy filtrowaniu danych z użyciem funkcji okien. Tak jak pisałem w poprzednim artykule znamienną cechą wyrażeń
CTE jest możliwość odwoływania się do samego siebie, co oznacza możliwość pisania zapytań rekurencyjnych. Cecha ta jest bardzo przydatna podczas odpytywania tabel, które posiadają relacje same do siebie. W tym artykule będę chciał przybliżyć pisanie właśnie takich konstrukcji, umożliwiających „chodzenie” po drzewie hierarchii w górę lub w dół.
Ponieważ składnię CTE poznaliśmy w poprzednim artykule przejdziemy od razu do konkretnego przykładu, dotyczącego tabeli Employees pochodzącej z bazy szkoleniowej T-SQL. Tabela posiada relację self-join opartą o pola empid oraz mgrid.
Taką relację możemy rozumieć jak odwołanie dwóch tabel o takich samych nazwach
Zastanówmy się na zapytaniem zwracającym numer przełożonego, imię, nazwisko i numer jego pracownika
Jak widać na załączonym powyżej zdjęciu zapytanie rekurencyjne CTE składa się z dwóch zapytań typu SELECT jest to podstawowa różnica pomiędzy zapytaniami opisanym w poprzednim artykule. Pierwsze zapytanie nazywane jest „kotwicą” i wskazuje na pierwszy rekord w hierarchii, po której chcemy się poruszać. W tym przypadku kluczowy jest warunek WHERE, w którym wskazujemy pracownika bez numery przełożonego, czyli początek hierarchii. Następnie zapytanie scalamy operatorem UNION z samym wyrażeniem CTE zachowując logikę przedstawioną na „rysunku 2”. Opcjonalnie do obu zapytań została dodana kolumna EmployeeLevel, która przedstawi numerycznie poziom pracownika w hierarchii. W efekcie użycia warunku WHERE w drugim zapytaniu „uruchamiamy” rekurencję, która wykonuje operację JOIN dla każdego pracownika. Wynik działania przedstawiam poniżej:
Mam nadzieję, ze przykład użycia rekurencyjnego zapytania CTE okazał się zrozumiały oraz przydatny. Więcej ciekawostek w kolejnych artykułach.
------------------------------------------------------
Autor:
Adam Białas – MCT, MCSE: Data Platform, Business Intelligence