рекурсия

Общее табличное выражение

Общее табличное выражение используют при работе с иерархиями.

Рассмотрим, как его использовать для улучшения читабельности наших запросов. Общее табличное выражение — позволяет отделить искомый товар и фильтрацию ненужных товаров в sql запросе.

Рассмотрим пример простого запроса:

SELECT ProductName, Price
FROM Products
WHERE CategoryID=1
AND ProductNAme LIKE 'D%'

В примере выше непонятно, сразу не понятно, какой конкретно товар мы ищем, и какие товары нам не нужны (их отфильтровываем).

Теперь используем общее табличное выражение:

--само табличное выражение (фильтрация ненужных товаров)
WITH MyTable(Name, Price) AS(
   SELECT ProductName, Price
   FROM Products
   WHERE CategoryID=1)
--какие конкретно товары ищем
SELECT *
FROM MyTable
WHERE Name LIKE 'D%'

В примере выше в скобках мы указываем sql запрос. После к результатам его отработки можно обращаться как к таблице MyTable с полями name, price. Теперь, глядя на запрос, ясно какие товары мы искали — которые начинаются на букву ‘D’.

Иерархия и общее табличное выражение

Рассмотрим на примере. Допустим есть база данных продавцов магазина. Есть заведующий магазином. Ему подчинаются старшие продавцы, а им — обычные продавцы-консультанты. Как в таблице сохранить иерархию продавцов?

Обычным запросом к БД мы не можем выбрать всех подчиненных зав. магазином, выберутся только его непосредственные подопечные (старшие продавцы). Также нельзя указать на конкретного сотрудника и узнать, кому он подчиняется (просмотреть всю его иерархичнскую лестницу).

Данный пример можно описать в виде некой рекурсии и подготовленных запросов, т.е. для каждого уровня иерархии написать свой запрос. Но если иерархическая лестница насчитывает 100 ступенек, то код получится очень громоздким.

А использование общего табличного выражения позволяет сделать все действия автоматически. Можем в БД использовать рекурсию + при описании табличного выражения можно на него ссылаться, даже если не до конца его написали. Пример:

WITH MyEmployees (ID, Name, ReportsTo, Level)
  AS(
   SELECT EmployeeID, FirstName+' '+LastName, ReportsTo,1
   FROM Employees
   WHERE ReportsTo IN NULL --якорь рекурсии, самый главный в магазине
     UNION ALL
   SELECT E.EmployeeID, E.FirstName+' '+E.LastName, E.ReportsTo, Level+1
   FROM emploees E INNER JOIN MyEmployees ME ON E.ReportsTo = ME.ID)
SELECT * FROM Employees

Т.е. сначала мы делаем первый select, находим главного в магазине (якорь рекурсии). Потом используем его для построения следующего select. Далее используем эти 2 select для построения третьего, четвертого и т.д., пока не будет возвращать пустые строки (значит иерархия закончилась). И уже потом можем выбирать иерархию по уровно:

SELECT * FROM MyEmployees WHERE Level=2



Добавить комментарий

Ваш e-mail не будет опубликован.