sql递归问题，在一行查询出父级的值

[LoveAndroid520]

insert into dept(dept_id，name，parent_dept_pid)values(1，'it'，null);
insert into dept(dept_id，name，parent_dept_pid)values(2，'软件开发'，1);
insert into dept(dept_id，name，parent_dept_pid)values(3，'报表开发'，2);

insert into dept(dept_id，name，parent_dept_pid)values(2，'软件开发'，1);
insert into dept(dept_id，name，parent_dept_pid)values(3，'web开发'，2);

insert into user(user_id，name，dept_id)values(885，'小明'，2);
insert into user(user_id，name，dept_id)values(455，'小张'，2);

真实环境shop栏位有50多个值，dept栏位值有200多个，科室有600多个，查询结果结构如下，sql怎样写？非常感谢！
name shop dept 科室
小明 it 软件开发 报表开发
小明 it 软件开发 web开发

[LoveAndroid520]

-----------------------------更新 insert into dept(dept_id，name，parent_dept_pid)values(1，'it'，null); insert into dept(dept_id，name，parent_dept_pid)values(2，'软件开发'，1); insert into dept(dept_id，name，parent_dept_pid)values(3，'报表开发'，2); insert into dept(dept_id，name，parent_dept_pid)values(2，'软件开发'，1); insert into dept(dept_id，name，parent_dept_pid)values(3，'web开发'，2); insert into user(user_id，name，dept_id)values(885，'小明'，2); insert into user(user_id，name，dept_id)values(455，'小张'，2); 真实环境shop栏位有50多个值，dept栏位值有200多个，科室有600多个，查询结果结构如下，sql怎样写？非常感谢！ name shop dept 科室 小明 it 软件开发 报表开发 小张 it 软件开发 web开发

[zjcxc]

按照你这个结果，那是固定 4 级了？

[二月十六]

如果级别固定可以直接jion获取父级数据，如果不固定，可以用cte获取。 小明和小张的科室是怎么确定的？

[zjcxc]

你这个数据似乎也不对 nsert into user(user_id，name，dept_id)values(885，'小明'，2); insert into user(user_id，name，dept_id)values(455，'小张'，2); 他们的 dept_id 都是2 ，对应的是 insert into dept(dept_id，name，parent_dept_pid)values(2，'软件开发'，1); 那从那个地方能够推得出来他们的科室？

[LoveAndroid520]

to：zjcxc不一定，有的员工只挂了1级，有的两级，。。。。。。。。。

[LoveAndroid520]

to：sinat_28984567 级别不确定

[LoveAndroid520]

to：zjcxc -----------------------------再更新 insert into dept(dept_id，name，parent_dept_pid)values(1，'it'，null); insert into dept(dept_id，name，parent_dept_pid)values(2，'软件开发'，1); insert into dept(dept_id，name，parent_dept_pid)values(3，'报表开发'，2); insert into dept(dept_id，name，parent_dept_pid)values(4，'web开发'，2); insert into user(user_id，name，dept_id)values(885，'小明'，3); insert into user(user_id，name，dept_id)values(455，'小张'，4); 真实环境shop栏位有50多个值，dept栏位值有200多个，科室有600多个，查询结果结构如下，sql怎样写？非常感谢！ name shop dept 科室 小明 it 软件开发 报表开发 小张 it 软件开发 web开发

[zjcxc]

引用 5 楼 LoveAndroid520 的回复:

to：zjcxc不一定，有的员工只挂了1级，有的两级，。。。。。。。。。

你这个结果： name shop dept 科室 就说明了带人一共 4 级， 如果是不固定级，那这些字段又如何对应？

[二月十六]

引用 6 楼 LoveAndroid520 的回复:

to：sinat_28984567 级别不确定

小明和小张的科室是怎么确定的？

[LoveAndroid520]

to：zjcxc栏位是固定，比如张三是经理，那他科室是空值，不知你理解我的意思了没有

[zjcxc]

use tempdb
go
create table dept(dept_id int, name nvarchar(10), parent_dept_pid int);
insert into dept(dept_id, name, parent_dept_pid)values(1, 'it', null);
insert into dept(dept_id, name, parent_dept_pid)values(2, '软件开发', 1);
insert into dept(dept_id, name, parent_dept_pid)values(3, '报表开发', 2);

insert into dept(dept_id, name, parent_dept_pid)values(4, 'web开发', 2);

create table [user](user_id int, name nvarchar(10), dept_id int);
insert into [user](user_id, name, dept_id)values(885, '小明', 3);
insert into [user](user_id, name, dept_id)values(455, '小张', 4);
insert into [user](user_id, name, dept_id)values(555, '小x', 2);

select distinct [user].name, shop.name as shop, 
	case when [user].dept_id in(科室.dept_id, dept.dept_id) then  dept.name end as dept,
	case when [user].dept_id in(科室.dept_id) then  科室.name end as 科室
from dept shop
	left join dept dept on dept.parent_dept_pid = shop.dept_id
	left join dept 科室 on 科室.parent_dept_pid = dept.dept_id
	inner join [user] on [user].dept_id in(科室.dept_id, dept.dept_id, shop.dept_id)
where shop.parent_dept_pid is null
/*
name       shop       dept       科室
---------- ---------- ---------- ----------
小x         it         软件开发       NULL
小明         it         软件开发       报表开发
小张         it         软件开发       web开发
*/

;with data as(
	select [user].name, dept.name as dept, dept.parent_dept_pid, level = 0
	from [user], dept
	where [user].dept_id = dept.dept_id
	union all
	select data.name, dept.name as dept, dept.parent_dept_pid, level = data.level+1
	from dept, data
	where dept.dept_id = data.parent_dept_pid
)
select name, [2] as shop, [1] as dept, [0] as 科室
from(select name, dept, level from data ) data
	pivot( max(dept) for level in ([0], [1], [2])) p
*--
name       shop       dept       科室
---------- ---------- ---------- ----------
小x         NULL       it         软件开发
小明         it         软件开发       报表开发
小张         it         软件开发       web开发
--*/
go
drop table dept, [user]

[zjcxc]

引用 9 楼 LoveAndroid520 的回复:

to：zjcxc栏位是固定，比如张三是经理，那他科室是空值，不知你理解我的意思了没有

那就是第1种写法