mssqlserver树形结构的存储与查询实例

sql code

--测试数据
create table #employees(
employeecode varchar(20) not null primary key clustered,
reporttocode varchar(20) null)
go
insert into #employees values('a',null)
insert into #employees values('b','a')
insert into #employees values('c','a')
insert into #employees values('d','a')
insert into #employees values('e','b')
insert into #employees values('f','b')
insert into #employees values('g','c')
insert into #employees values('h','d')
insert into #employees values('i','d')
insert into #employees values('j','d')
insert into #employees values('k','j')
insert into #employees values('l','j')
insert into #employees values('m','j')
insert into #employees values('n','k')
go

/*
可能遇到的查询问题：
1. 员工'd'的所有直接下属
2. 员工'd'的所有2级以内的下属（包括直接下属和直接下属的下属）
3. 员工'n'的所有上级（按报告线顺序列出）
4. 员工@employeecode的所有@leveldown级以内的下属（@employeecode和@leveldown以变量传入）
declare @employeecode varchar(20), @leveldown int;
set @employeecode = 'd';
set @leveldown = 2;
5. 员工@employeecode的所有@levelup级以内的上级（@employeecode和@levelup以变量传入）
declare @employeecode varchar(20), @levelup int;
set @employeecode = 'n';
set @levelup = 2;
*/
--用递归cte实现员工树形关系表

with cte as(
select
employeecode,
reporttocode,
reporttodepth = 0,
reporttopath = cast('/' + employeecode + '/' as varchar(200))
from #employees
where reporttocode is null
union all
select
e.employeecode,
e.reporttocode,
reporttodepth = mgr.reporttodepth + 1,
reporttopath = cast(mgr.reporttopath + e.employeecode + '/' as varchar(200))
from #employees e
inner join cte mgr
on e.reporttocode = mgr.employeecode
)
select * from cte order by reporttopath