如何获取树方式结果集

Question

我的表结构

ID   Name      ParentID 
1    Master       0
2    Employee     1
3    Leave        2
4     Admin       1

我需要结果是

ID   Name      ParentID    Result
1    Master       0       Master
2    Employee     1       Master/Employee
3    Leave        2       Master/Employee/Leave
4    Admin        1       Master/Admin        

Answer 1

您可以使用

如下所示的查询：

观看现场演示

create table sample (ID int,   Name varchar(100),      ParentID  int)
insert into sample values
(1,'Master', 0)
,(2,'Employee', 1)
,(3,'Leave', 2)
,(4,'Admin', 1)
;
WITH tb  (id,Name, parentid, Path)
AS
(
 SELECT 
    id,
    Name,  parentid,
    CAST('/'+Name as nvarchar(max)) as Path
 FROM sample
 WHERE ParentID=0
 UNION All
 SELECT 
    e.id,e.Name, e.parentid, x.Path + '/' + e.Name as Path
 FROM sample e
 JOIN tb x ON x.id = e.ParentID

)
SELECT * FROM tb