mssql server
树形布局的积累与查询实例数据库教程设计中时时会遇上须求仓库储存树形构造,比如职工涉嫌表、协会布局表,等等。

inner join 连接多个数据表的用法:select * from ((表1 inner join 表2 on
表1.字段号=表2.字段号) inner join 表3 on 表1.字段号=表3.字段号) inner
join 表4 on member.字段号=表4.字段号

/*恐怕境遇的询问难点:1. 职员和工人’d’的富有直接下属2.
工作者’d’的具有2级以内的下属3. 职员和工人’n’的持有上级4.
职员和工人@employeecode的持有@leveldown级以内的上边declare @employeecode
varchar(20卡塔尔(قطر‎, @leveldown int;set @employeecode = ‘d’;set @leveldown =
2;5. 职员和工人@employeecode的有着@levelup级以内的上面declare @employeecode
varchar(20State of Qatar, @levelup int;set @employeecode = ‘n’;set @levelup =
2;*/–用递归cte落成职员和工人树形关系表

总结

–测验数据create table #employees( employeecode varchar(20) not null
primary key clustered, reporttocode varchar(20) null)goinsert 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

inner join 连接多个数据表的用法:select * from 表1 inner join 表2 on
表1.字段号=表2.字段号

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

在表中留存最少一个非凡时,inner join 关键字重回行。

sql code

叁个表连接

inner join 连接八个数据表的用法:select * from (((表1 inner join 表2 on
表1.字段号=表2.字段号) inner join 表3 on 表1.字段号=表3.字段号) inner
join 表4 on member.字段号=表4.字段号) inner join 表5 on
member.字段号=表5.字段号

相关文章