Вчера я ответил на рекурсивное CTE, которое выявило проблему с тем, как они реализованы в SQL Server (возможно, и в других СУБД тоже?). По сути, когда я пытаюсь использовать ROW_NUMBER
для текущего рекурсивного уровня, он выполняется для каждого подмножества строк текущего рекурсивного уровня. Я ожидаю, что это будет работать в истинной логике SET и работать против всего текущего рекурсивного уровня.
Похоже, что из этой статьи MSDNпроблема, которую я обнаружил, связана с предполагаемой функциональностью:
Аналитические и агрегатные функции в рекурсивной части CTE применяется к набору для текущего уровня рекурсии, а не к набору для КТЭ. Такие функции, как ROW_NUMBER, работают только с подмножеством данные, переданные им текущим уровнем рекурсии, а не всей набор данных, передаваемых в рекурсивную часть CTE. Для большего информацию см. в J. Использование аналитических функций в рекурсивном CTE.
Копаясь, я нигде не смог найти объяснения, почему это было выбрано для работы именно так, а не иначе? Это скорее процедурный подход в языке, основанном на наборах, так что это противоречит моему мыслительному процессу SQL и, на мой взгляд, довольно запутанно. Кто-нибудь знает и/или может объяснить, почему рекурсивный CTE обрабатывает аналитические функции на уровне рекурсии процедурным образом?
Вот код, помогающий визуализировать это:
Обратите внимание, столбец RowNumber
в каждом из этих выходных данных кода.
Вот SQLFiddle для CTE (показывает только 2-й уровень рекурсии)
WITH myCTE
AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, 1 AS RecurseLevel
FROM tblGroups
WHERE ParentId IS NULL
UNION ALL
SELECT tblGroups.*,
ROW_NUMBER() OVER (ORDER BY myCTE.RowNumber , tblGroups.Score desc) AS RowNumber,
RecurseLevel + 1 AS RecurseLevel
FROM tblGroups
JOIN myCTE
ON myCTE.GroupID = tblGroups.ParentID
)
SELECT *
FROM myCTE
WHERE RecurseLevel = 2;
WITH myCTE
AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, 1 AS RecurseLevel
FROM tblGroups
WHERE ParentId IS NULL
)
SELECT tblGroups.*,
ROW_NUMBER() OVER (ORDER BY myCTE.RowNumber , tblGroups.Score desc) AS RowNumber,
RecurseLevel + 1 AS RecurseLevel
FROM tblGroups
JOIN myCTE
ON myCTE.GroupID = tblGroups.ParentID;
Я всегда предполагал, что рекурсивный CTE SQL будет работать больше как этот цикл while
DECLARE @RecursionLevel INT
SET @RecursionLevel = 0
SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, @RecursionLevel AS recurseLevel
INTO #RecursiveTable
FROM tblGroups
WHERE ParentId IS NULL
WHILE EXISTS( SELECT tblGroups.* FROM tblGroups JOIN #RecursiveTable ON #RecursiveTable.GroupID = tblGroups.ParentID WHERE recurseLevel = @RecursionLevel)
BEGIN
INSERT INTO #RecursiveTable
SELECT tblGroups.*,
ROW_NUMBER() OVER (ORDER BY #RecursiveTable.RowNumber , tblGroups.Score desc) AS RowNumber,
recurseLevel + 1 AS recurseLevel
FROM tblGroups
JOIN #RecursiveTable
ON #RecursiveTable.GroupID = tblGroups.ParentID
WHERE recurseLevel = @RecursionLevel
SET @RecursionLevel = @RecursionLevel + 1
END
SELECT * FROM #RecursiveTable ORDER BY RecurseLevel;