1,在现实世界中，有很多现象存在层次结构，公司的人事职称是典型的层次结果，如下图
Sql Server是关系型DB，适合存储二维关系的数据，如何存储具有层次结构的数据了？需要使用一个字段ParentID表示上级ID，示例表结构如下
create table dbo.emph ( ID int not null primary key, ParentID int foreign key references dbo.emph(id), Descr varchar(100) not null )
2,插入示例数据
insert into dbo.emph(id,ParentID,Descr) values(1,null,'boss'), (2,1,'M1'),(3,1,'M2'),(4,1,'M3'),(5,1,'M4'), (6,2,'L11'),(20,2,'L12'),(7,2,'L13'),(8,2,'L14'),(9,2,'L15'), (10,3,'L21'),(11,3,'L22'),(12,3,'L23'),(14,3,'L24'), (15,6,'E111'),(16,6,'E112'),(17,6,'E113'),(18,6,'E114'), (19,20,'E121'),(21,20,'E122'),(22,20,'E123')
3,使用CTE递归查询M1手下的所有员工，包括Leader和Employee
;with cte(id,parentid,descr) as ( select id,parentid,descr from dbo.emph where id=2 union all select e.ID,e.ParentID,e.Descr from dbo.emph e inner join cte c on e.ParentID=c.id ) select * from cte order by parentid
4,查看查询嵌套的Level，示例代码如下
;with cte(id,parentid,descr,Level) as ( select id,parentid,descr,0 as Level from dbo.emph where id=2 union all select e.ID,e.ParentID,e.Descr,Level+1 as Level from dbo.emph e inner join cte c on e.ParentID=c.id ) select * from cte order by parentid
5,查看每一行数据的Path，便于查看归属关系，path是使用ID来界定的
;with cte(path,id,parentid,descr,Level) as ( select cast('\'+cast(id as varchar) as varchar(100)) as path,
id,parentid,descr,0 as Level from dbo.emph where id=2 union all select cast(c.path+'\'+ cast(e.ID as varchar) as varchar(100)) as path, e.ID,e.ParentID,e.Descr,Level+1 as Level from dbo.emph e inner join cte c on e.ParentID=c.id ) select * from cte order by parentid
推荐文档：
作者：悦光阴
本文版权归作者和博客园所有，欢迎转载，但未经作者同意，必须保留此段声明，且在文章页面醒目位置显示原文连接，否则保留追究法律责任的权利。