时间戳

首页 > 解决方案 > 如何编写SQL语句返回每个根的所有孩子，扩展到每个根

sql - 如何编写SQL语句返回每个根的所有孩子，扩展到每个根

问题描述

我有一张桌子。[contents] 的值可以插入任何单词（以下仅为示例）

create table #TreeTable (
     Id uniqueidentifier,       
     Contents NVARCHAR(200),
     ParentId uniqueidentifier,
     Floors Int,
     Sort Int,
)

  insert into #TreeTable([Id], [ParentId], [Contents],[Floors],[Sort]) values
    (1, 0, '1',1,1),
    (2, 0, '2',1,2),
    (3, 1, '1-1',2,1),
    (4, 1, '1-2',2,2),
    (5, 2, '2-1',2,1),
    (6, 3, '1-1-1',3,1),
    (7, 4, '1-2-1',3,1);
    (8, 4, '1-2-2',3,2),
    (9, 6, '1-1-1-1',4,1),
    (10, 6,'1-1-1-2',4,2);

我想编写一个 T-SQL 语句，该语句将返回具有根 ID 的每个根的所有子节点，下面是我期望的结果

[Id][ParentId][Contents] [Levels][Sort]
(1,   0,       '1',       1,      1)
(3,   1,       '1-1',     2,      1)
(6,   3,       '1-1-1',   3,      1)
(9,   6,       '1-1-1-1', 4,      1)
(10,  6,       '1-1-1-2', 4,      2)
(4,   1,       '1-2',     2,      2)
(7,   4,       '1-2-1',   3,      1)
(8,   4,       '1-2-2',   3,      2)
(2,   0,       '2',       1,      2)
(5,   2,       '2-1',     2,      1)

树的深度可以任意长

我尝试使用这个，但结果不是我所期望的

with RecursiveTable as(
        ------ start-------
      SELECT a.*
      FROM TreeTable a
     WHERE
      a.ParentId  = 0
       ------ end ---------
        union all
      SELECT b.*
      FROM TreeTable b
      join RecursiveTable a on a.Id =b.ParentId
        )
     select * from RecursiveTable

标签： sqlsql-server

解决方案

如果我们可以相信[Contents]价值观，那么只需

SELECT [Id], 
       [ParentId],
       [Contents],
       LEN([Contents]) - LEN(REPLACE([Contents], '-', '')) + 1 [Levels],
       [Sort]
FROM #TreeTable
ORDER BY [Contents];

小提琴

如果没有那么

WITH cte AS 
(
SELECT [Id], [ParentId], CAST([Sort] AS NCHAR), [Sort], 1 [Levels]
FROM #TreeTable
WHERE [ParentId] = 0
UNION ALL
SELECT t.[Id], t.[ParentId], cte.[Contents] + '-' + t.[Sort], t.[Sort], 1 + cte.[Levels]
FROM #TreeTable t
JOIN cte ON t.[ParentId] = cte.[Id]
)
SELECT [Id], [ParentId], [Contents], [Levels], [Sort]
FROM cte
ORDER BY [Contents];

小提琴（自己优化类型以适应它们在 CTE 中的匹配）。

推荐阅读