Рекурсивные SQL запросы
CTE(обобщенное табличное выражение) может ссылаться на себя, создавая рекурсивное CTE.
Рекурсивное CTE многократно выполняется, чтобы возвращать подмножество данных до тех пор, пока не получится конечный результирующий набор.
Обычно рекурсивные запросы используются для возвращения иерархических данных, например: отображение сотрудников в структуре организации или генерация последовательности.
Структура рекурсивного CTE
WITH cte_name ( column_name [,...n] ) AS ( CTE_query_definition –- Anchor member is defined. UNION ALL CTE_query_definition –- Recursive member is defined referencing cte_name. ) -- Statement using the CTE SELECT * FROM cte_name
CTE разбивается на закрепленный и рекурсивный элементы. Запускается закрепленный элемент с созданием первого вызова. Рекурсивный элемент ссылается на закрепленный и вызывается пока не вернет пустой набор.
Классический пример с сотрудниками
DECLARE @Employees TABLE ( ID int NOT NULL, [Name] nvarchar(200) NOT NULL, ManagerID int NULL ) INSERT INTO @Employees VALUES (1, N'Ken', NULL) ,(2, N'Brian',1) ,(3, N'Stephen', 2) ,(4, N'Michael', 2) ,(5, N'Linda', 3) ,(6, N'Syed', 4) ,(7, N'Lynn', 5) ,(8, N'David', NULL) ,(9, N'Mary', 8);
ManagerID - прямой руководитель.
В данном случае у нас два босса Ken и David, необходимо получить всех сотрудников отдела Кена.
WITH DirectReports (ID, [Name], ManagerID, Level) AS ( -- Anchor member definition SELECT e.ID, e.[Name], ManagerID, 0 AS Level FROM @Employees AS e WHERE e.ID = 1 UNION ALL -- Recursive member definition SELECT e.ID, e.[Name], e.ManagerID, Level + 1 FROM @Employees AS e INNER JOIN DirectReports AS d ON e.ManagerID = d.ID ) -- Statement that executes the CTE SELECT ID, [Name], ManagerID, Level FROM DirectReports
Прописные латинские буквы
Так же часто появляется необходимость в генерации различных последовательностей.;WITH Letters AS( SELECT ASCII('A') code, CHAR(ASCII('A')) letter UNION ALL SELECT code+1, CHAR(code+1) FROM Letters WHERE code+1 <= ASCII('Z') ) SELECT letter FROM Letters;
Числовая последовательность
DECLARE @startnum INT=1 DECLARE @endnum INT=55 ; WITH gen AS ( SELECT @startnum AS num UNION ALL SELECT num+1 FROM gen WHERE num+1<=@endnum ) SELECT * FROM gen option (maxrecursion 55)
Подсказка MAXRECURSION может использоваться для предотвращения входа в бесконечный цикл из-за неверно сформированного рекурсивного CTE-выражения.
Объединение временных интервалов
Есть справочник с ценами на продукты, для оптимизации объедением смежные интервалыDECLARE @ProductPrice TABLE ( ProductID int, Price money, BeginDate date, EndDate date NULL ) INSERT INTO @ProductPrice VALUES (1, 11.11, '20170101', '20170215') ,(1, 11.11, '20170216', '20170615') ,(1, 22.1, '20170616', null) ,(2, 33, '20170101', '20170201') ,(2, 33, '20170501', '20170601') ,(3, 12, '20170101', '20170215') ,(3, 12, '20170216', null);
Нужно получить в таком виде
--1 11 '20170101' '20170615' --1 22.1 '20170616' null --2 33 '20170101' '20170201' --2 33 '20170501' '20170601' --3 12 '20170101' null
Одно из решений это рекурсия в CTE
;with cte as ( select a.ProductID, a.Price, a.BeginDate, a.EndDate from @ProductPrice a left join @ProductPrice b on a.ProductID=b.ProductID and dateadd(day,-1,a.BeginDate)=b.EndDate and a.Price=b.Price where b.BeginDate is null union all select a.ProductID, a.Price, a.BeginDate, b.EndDate from cte a join @ProductPrice b on a.ProductID=b.ProductID and dateadd(day,-1,b.BeginDate)=a.EndDate and a.Price=b.Price ) select ProductID, Price, BeginDate, nullif(max(isnull(EndDate,'32121231')),'32121231') EndDate from cte group by ProductID, Price, BeginDate
Комментарии
Отправить комментарий