Рекурсия CTE SQL Server 2008 года
Я пытаюсь выполнить то, чему я верю, трудная рекурсия с помощью CTE, SQL Server 2008. Я, может казаться, не переношу голову вокруг этого.
В ниже примеров можно принять фиксированную глубину 3... ничто никогда не будет ниже, чем это. В реальной жизни "глубже" но все еще фиксируется глубина. В примере я пытался упростить его некоторые.
Мои входные данные похожи ниже.
ID PARENT_ID NAME DEPTH
------------------------------------------
1 NULL A 1
2 1 B 2
3 2 C 3
4 1 D 2
Вывод моего CTE должен быть следующей таблицей.
LEVEL1_ID LEVEL2_ID LEVEL3_ID LEVEL1_NAME LEVEL2_NAME LEVEL3_NAME
--------------------------------------------------------------------------------
1 NULL NULL A NULL NULL
1 2 NULL A B NULL
1 2 3 A B C
1 4 NULL A D NULL
Если я могу получить столбцы ID в выводе, я могу, конечно, отобразиться на имена в справочной таблице.
Я открыт для других способов выполнить это также, включая использование SSIS.
2 ответа
На самом деле это не так уж и сложно:
;WITH cte AS
(
SELECT CAST('/' + Name AS VARCHAR(50)) as 'CteName', ID
FROM dbo.YourTable
WHERE parent_id IS NULL
UNION ALL
SELECT CAST(cte.CteName + '/' + Name AS VARCHAR(50)), t.ID
FROM dbo.YourTable t
INNER JOIN cte ON t.parent_id = cte.id
)
SELECT cteName FROM cte
ORDER BY ID
Дает мне результат:
/A
/A/B
/A/B/C
/A/D
В качестве примечания: «глубину» можно легко вычислить с помощью CTE, и вы не должны Не обязательно хранить это в своей таблице (см. столбец Уровень , который я добавил):
;WITH cte AS
(
SELECT
CAST('/' + Name AS VARCHAR(50)) as 'CteName', ID,
1 AS 'Level'
FROM dbo.YourTable
WHERE parent_id IS NULL
UNION ALL
SELECT
CAST(cte.CteName + '/' + Name AS VARCHAR(50)), t.ID,
cte.Level + 1 AS 'Level'
FROM dbo.YourTable t
INNER JOIN cte ON t.parent_id = cte.id
)
SELECT cteName FROM cte
ORDER BY Level, ID
Я не помню, чтобы вы могли выполнять подзапрос в cte.
У меня здесь нет копии sql server, но вы можете попробовать с помощью этого кода:
WITH cte(id, path, level)
AS
(
SELECT id, '/' + name, level
FROM yourtable
WHERE level = 1
UNION ALL
SELECT y.id, c.name + '/' + y.name, y.level
FROM yourtable y INNER JOIN
cte c ON c.id = y.parent_id
WHERE level = (SELECT max(level)+1 from cte)
)
SELECT path from cte