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

mssql server 树形结构的存储与查询实例
设计中常常会遇到需要存储树形结构，比如员工关系表、组织结构表，等等。

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