with Department(GuidCode,DepName,ParentGuid) as( select 1,'总部',0 union all select 2,'分部1',1 union all select 3,'分部2',1 union all select 4,'分部3',2 union all select 5,'分部4',2 union all select 6,'分部5',3 union all select 7,'分部6',3 ), Users(GuidCode,UserName,DepartmentGuid) as( select 0,'老大',1 union all select 1,'小一',3 union all select 2,'小二',3 union all select 3,'小三',2 union all select 4,'小四',2 union all select 5,'小五',6 union all select 6,'小刘',6 ), Orders(GuidCode,OrderName,UserGuid) as( select 0,'订单1',1 union all select 1,'订单2',1 union all select 2,'订单3',1 union all select 3,'订单4',1 union all select 4,'订单5',2 union all select 5,'订单6',2 union all select 6,'订单7',4 union all select 7,'订单8',1 union all select 8,'订单9',5 union all select 9,'订单10',5 union all select 10,'订单11',1 union all select 11,'订单12',6 union all select 12,'订单13',6 union all select 13,'订单14',1 union all select 14,'订单15',2 union all select 15,'订单16',3 union all select 16,'订单17',3 union all select 17,'订单18',3 ), tb as( select * from Department where DepName='分部2' union all select t.* from Department t join tb c on c.GuidCode=t.ParentGuid ) select c.* from tb a join Users b on a.GuidCode=b.DepartmentGuid join Orders c on b.GuidCode=c.UserGuid
as(
select 1,'总部',0 union all
select 2,'分部1',1 union all
select 3,'分部2',1 union all
select 4,'分部3',2 union all
select 5,'分部4',2 union all
select 6,'分部5',3 union all
select 7,'分部6',3
),
Users(GuidCode,UserName,DepartmentGuid)
as(
select 0,'老大',1 union all
select 1,'小一',3 union all
select 2,'小二',3 union all
select 3,'小三',2 union all
select 4,'小四',2 union all
select 5,'小五',6 union all
select 6,'小刘',6
),
Orders(GuidCode,OrderName,UserGuid)
as(
select 0,'订单1',1 union all
select 1,'订单2',1 union all
select 2,'订单3',1 union all
select 3,'订单4',1 union all
select 4,'订单5',2 union all
select 5,'订单6',2 union all
select 6,'订单7',4 union all
select 7,'订单8',1 union all
select 8,'订单9',5 union all
select 9,'订单10',5 union all
select 10,'订单11',1 union all
select 11,'订单12',6 union all
select 12,'订单13',6 union all
select 13,'订单14',1 union all
select 14,'订单15',2 union all
select 15,'订单16',3 union all
select 16,'订单17',3 union all
select 17,'订单18',3
),
tb as(
select * from Department where DepName='分部2' union all
select t.* from Department t join tb c on c.GuidCode=t.ParentGuid
)
select c.* from tb a join Users b on a.GuidCode=b.DepartmentGuid join Orders c on b.GuidCode=c.UserGuid