Как работает рекурсивный CTE, построчно?
Я думаю, что у меня есть формат рекурсивных CTE, достаточно хорошо, чтобы написать его, но все же считаю, что я разочарован тем, что я не могу его вручную обработать (притворись, что я сам SQL-движок, и дойду до набора результатов с помощью пера и бумага). Я нашел это, что близко к тому, что я ищу, но недостаточно подробно. У меня нет проблем отслеживать рекурсивную функцию С++ и понимать, как она работает, но для SQL я не понимаю, почему и как движок знает, что нужно остановить. Является ли привязка и рекурсивный блок вызываться каждый раз, или якорь пропущен в последующих итерациях? (Я сомневаюсь в этом, но я пытаюсь выразить свое замешательство в том, как он, кажется, прыгает.) Если якорь вызывается каждый раз, как якорь не появляется несколько раз в конечном результате? Я надеюсь, что кто-то может просто пробить линию 1 линии 2 и т.д., Что произойдет, а что "в памяти" по мере накопления результирующего набора.
Я взял на себя смелость украсть мой пример с этой страницы, так как это кажется самым легким для понимания.
DECLARE @tbl TABLE (
Id INT
, [Name] VARCHAR(20)
, ParentId INT
)
INSERT INTO @tbl( Id, Name, ParentId )
VALUES
(1, 'Europe', NULL)
,(2, 'Asia', NULL)
,(3, 'Germany', 1)
,(4, 'UK', 1)
,(5, 'China', 2)
,(6, 'India', 2)
,(7, 'Scotland', 4)
,(8, 'Edinburgh', 7)
,(9, 'Leith', 8)
;
WITH abcd
AS (
-- anchor
SELECT id, Name, ParentID,
CAST(Name AS VARCHAR(1000)) AS Path
FROM @tbl
WHERE ParentId IS NULL
UNION ALL
--recursive member
SELECT t.id, t.Name, t.ParentID,
CAST((a.path + '/' + t.Name) AS VARCHAR(1000)) AS "Path"
FROM @tbl AS t
JOIN abcd AS a
ON t.ParentId = a.id
)
SELECT * FROM abcd
Ответы
Ответ 1
Подумайте о рекурсивном CTE как о бесконечном UNION ALL:
WITH rows AS
(
SELECT *
FROM mytable
WHERE anchor_condition
),
rows2 AS
(
SELECT *
FROM set_operation(mytable, rows)
),
rows3 AS
(
SELECT *
FROM set_operation(mytable, rows2)
),
…
SELECT *
FROM rows
UNION ALL
SELECT *
FROM rows2
UNION ALL
SELECT *
FROM rows3
UNION ALL
…
В вашем случае это будет:
WITH abcd1 AS
(
SELECT *
FROM @tbl t
WHERE ParentId IS NULL
),
abcd2 AS
(
SELECT t.*
FROM abcd1
JOIN @tbl t
ON t.ParentID = abcd1.id
),
abcd3 AS
(
SELECT t.*
FROM abcd2
JOIN @tbl t
ON t.ParentID = abcd2.id
),
abcd4 AS
(
SELECT t.*
FROM abcd3
JOIN @tbl t
ON t.ParentID = abcd3.id
),
abcd5 AS
(
SELECT t.*
FROM abcd4
JOIN @tbl t
ON t.ParentID = abcd4.id
),
abcd6 AS
(
SELECT t.*
FROM abcd5
JOIN @tbl t
ON t.ParentID = abcd5.id
)
SELECT *
FROM abcd1
UNION ALL
SELECT *
FROM abcd2
UNION ALL
SELECT *
FROM abcd3
UNION ALL
SELECT *
FROM abcd4
UNION ALL
SELECT *
FROM abcd5
UNION ALL
SELECT *
FROM abcd6
Так как abcd6 не дает результатов, это означает условие остановки.
Теоретически рекурсивный CTE может быть бесконечным, но практически, SQL Server пытается запретить запросы, которые приведут к бесконечным наборам записей.
Вы можете прочитать эту статью:
Ответ 2
Алгоритм использования CTE:
- Выполнить анкерную часть, получить результат r0
- Выполните рекурсивную часть, используя r0 в качестве ввода, и получите результат r1 (не null)
- Выполните рекурсивную часть, используя r1 в качестве ввода, и получите результат r2 (не null)
- Выполните рекурсивную часть, используя r3 в качестве ввода, и получите результат r3 (не null)
...
- Выполните рекурсивную часть, используя r (n-1) в качестве входных данных и выведите rn (null). На этот раз rn имеет значение null, поэтому мы используем UNION ALL для объединения r0, r1, r2... r (n-1) и что конечный результат
Давайте возьмем пример:
WITH cte ( value )
AS (
SELECT 1
UNION ALL
SELECT value + 1
FROM cte
WHERE value < 4
)
SELECT *
FROM cte
Результат этого запроса:
value
-----------
1
2
3
4
(4 row(s) affected)
Давайте рассмотрим его шаг за шагом:
Execute anchor query (SELECT 1), we got:
r0 = 1
cte = r0 = 1
|
|
V
Now we execute
SELECT value + 1 FROM cte WHERE value < 4
Since cte is r0 (only has 1), we got:
r1 = 2
cte = r1 = 2
|
|
V
Now we execute
SELECT value + 1 FROM cte WHERE value < 4
Since cte is r1 (only has 2), we got:
r2 = 3
cte = r2 = 3
|
|
V
Now we execute
SELECT value + 1 FROM cte WHERE value < 4
Since cte is r2 (only has 3), we got:
r3 = 4
cte = r3 = 4
|
|
V
Now we execute
SELECT value + 1 FROM cte WHERE value < 4
Since cte is r3 (only has 4), we got:
r4 = NULL (because r3 (4) is equal to 4, not less than 4)
Now we stop the recursion!
|
|
V
Let calculate the final result:
R = r0 union all
r1 union all
r2 union all
r3 union all
= 1 union all
2 union all
3 union all
4 union all
= 1
2
3
4
Ответ 3
Я думаю, что это ломается вот так:
-
Выполняется оператор привязки. Это дает вам набор результатов, называемых базовым набором, или T0.
-
Рекурсивный оператор выполняется, используя T0 в качестве таблицы для выполнения запроса. Это происходит автоматически при запросе CTE.
-
Если рекурсивный элемент возвращает некоторые результаты, он создает новый набор T1. Рекурсивный элемент затем выполняется снова, используя T1 в качестве входных данных, создавая T2, если есть какие-либо результаты.
-
Шаг 3 продолжается до тех пор, пока не будет создано больше результатов, или будет выполнено максимальное количество рекурсий, заданное параметром MAX_RECURSION.
Эта страница, вероятно, объясняет это лучше всего. Он имеет пошаговое руководство по пути выполнения CTE.
Ответ 4
Вероятно, вам захотелось эта ссылка. Нет, якорь не выполняется несколько раз (этого не может быть, тогда union all потребует появления всех результатов). Подробности по предыдущей ссылке.
Ответ 5
Шаг 1:
1 Europe NULL Europe
2 Asia NULL Asia
Шаг 2:
1 Europe NULL Europe
2 Asia NULL Asia
3 Germany 1 Europe/Germany
4 UK 1 Europe/UK
5 China 2 Asia/China
6 India 2 Asia/India
Шаг 3:
1 Europe NULL Europe
2 Asia NULL Asia
3 Germany 1 Europe/Germany
4 UK 1 Europe/UK
5 China 2 Asia/China
6 India 2 Asia/India
7 Scotland 4 Europe/UK/Scotland
Шаг 4:
1 Europe NULL Europe
2 Asia NULL Asia
3 Germany 1 Europe/Germany
4 UK 1 Europe/UK
5 China 2 Asia/China
6 India 2 Asia/India
7 Scotland 4 Europe/UK/Scotland
8 Edinburgh 7 Europe/UK/Scotland/Edinburgh
Шаг 5:
1 Europe NULL Europe 0
2 Asia NULL Asia 0
3 Germany 1 Europe/Germany 1
4 UK 1 Europe/UK 1
5 China 2 Asia/China 1
6 India 2 Asia/India 1
7 Scotland 4 Europe/UK/Scotland 2
8 Edinburgh 7 Europe/UK/Scotland/Edinburgh 3
9 Leith 8 Europe/UK/Scotland/Edinburgh/Leith 4
Последний столбец на шаге 5 - это уровень. На каждом уровне строки добавляются в отношении того, что уже доступно. Надеюсь, это поможет.
