Рекурсивные 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