T_Dep(部门表)
dep_id p_depid dep-nameT_Emp(职工表)
emp-id dep-id emp-name p_depid 是上一级部门id 如果有的话就关联 dep_id
dep_id 是部门id查询软件一部及下属部门的所有员工的sql语句
thks
dep_id p_depid dep-nameT_Emp(职工表)
emp-id dep-id emp-name p_depid 是上一级部门id 如果有的话就关联 dep_id
dep_id 是部门id查询软件一部及下属部门的所有员工的sql语句
thks
;with cte as
(
select dep_id,p_depid
from t_dep
where dep_name = '软件一部'
union all
select a.dep_id,a.p_depid
from t_dep a join cte b on a.p_depid = b.dep_id
)select b.*
from cte a join t_emp b on a.dep_id = b.dep_id
1 0 软件一部
2 1 软件一部下属一部
--try
;with t as
(
select 1 leave,emp_name,dep_name,b.p_depid,b.dep_id from T_Emp a
inner join T_Dep b on a.dep_id=b.dep_id and b.dep_name='软件一部'
)
,
tt
as
(
select leave,emp_name,dep_name,p_depid,dep_id from t
union all
select leave+1 leave,emp_name,dep_name,b.p_depid from t
inner join tt on t.p_depid=tt.dep_id
)
select emp_name,dep_name from tt order by leave
with,本身不是递归,但是在with语句中使用cte这个结果集,也就是在循环查询了。
insert into @T_Dep
select 1,0,N'软件部' union all
select 2,1,N'软件一部' union all
select 3,2,N'软件一部一组' union all
select 4,2,N'软件一部二组' union all
select 5,1,N'软件二部' union all
select 6,5,N'软件二部一组' union all
select 7,5,N'软件二部二组' union all
select 8,3,N'软件一部一组开发' union all
select 9,3,N'软件一部一组测试'
declare @T_Emp table(emp_id int,dep_id int,emp_name nvarchar(20))
insert into @T_Emp
select 1,2,'aaa' union all
select 2,3,'bbb' union all
select 3,4,'ccc' union all
select 4,6,'ddd' union all
select 5,7,'eee' union all
select 6,8,'ggg' union all
select 7,9,'iii';with t as
(
select emp_name,dep_name,b.p_depid,b.dep_id from @T_Emp a
inner join @T_Dep b on a.dep_id=b.dep_id --and b.dep_name='软件一部'
)
,
tt
as
(
select 1 as leave,emp_name,dep_name,p_depid,dep_id from t where dep_name=N'软件一部'
union all
select tt.leave+1 as leave,t.emp_name,t.dep_name,t.p_depid,t.dep_id from t
inner join tt on t.p_depid=tt.dep_id
)select leave,emp_name,dep_name from tt order by leave asc/*
leave emp_name dep_name
----------- -------------------- --------------------
1 aaa 软件一部
2 bbb 软件一部一组
2 ccc 软件一部二组
3 ggg 软件一部一组开发
3 iii 软件一部一组测试
*/