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
