请教一个获得所有上级部门的sql语句，希望尽可能优化

[cyc123007512]

create table testdept(
deptid int identity(1,1) primary key,
deptname varchar(16),
superdept int
);


insert into testdept(deptname,superdept) values('总经办',0);
insert into testdept(deptname,superdept) values('财务部',1);
insert into testdept(deptname,superdept) values('工程部',1);
insert into testdept(deptname,superdept) values('会计部',2);
insert into testdept(deptname,superdept) values('出纳部',2);
insert into testdept(deptname,superdept) values('北京工程办',3);
insert into testdept(deptname,superdept) values('南京工程办',3);

希望查询出这样的结果：
deptid deptname superdept
0 总经办 总经办
1 工程部 总经办
2 财务部 总经办
3 会计部 财务部
4 出纳部 财务部
5 北京工程办 工程部
6 南京工程办 工程部

[--小F--]

/*----------------------------------------------
标题：SQL2005 BOM递归方法整理
（以公司组织架构为例）

整理人：htl258(Tony)

日期:2009.04.25(引用请保留此信息)
------------------------------------------------*/
-->创建测试环境
DECLARE @t TABLE
(
FullDept VARCHAR(20), --部门全称
Dept VARCHAR(20), --部门
ParentDept VARCHAR(20), --上级部门
Supervisor VARCHAR(20) --部门主管
)
INSERT @t SELECT 'S-IT' ,'IT','S' ,'Peter'
UNION ALL SELECT 'S-IT-CN' ,'CN','S-IT' ,'Mary' 
UNION ALL SELECT 'S-IT-CN-SH' ,'SH','S-IT-CN' ,'Jack' 
UNION ALL SELECT 'S-FS-AP' ,'AP','S-FS' ,'Colin' 
UNION ALL SELECT 'S-FS' ,'FS','S' ,'Jerry' 
UNION ALL SELECT 'S' ,'0' ,'0' ,'CiCi' 
/*
SELECT * FROM @t
--------------------
FullDept(部门全称) Dept(部门) ParentDept(上级部门) Supervisor(部门主管)
-------------------- -------------------- -------------------- --------------------
S-IT IT S Peter
S-IT-CN CN S-IT Mary
S-IT-CN-SH SH S-IT-CN Jack
S-FS-AP AP S-FS Colin
S-FS FS S Jerry
S 0 0 CiCi
(6 行受影响)
*/
-->1.使用CTE递归返回指定根的子树查询：
--->例1.查询部门主管为"Peter"的所有下级部门清单
;WITH t AS 
(
SELECT *,0 as lvl /*展开层次*/ FROM @t WHERE Supervisor = 'Peter'
UNION ALL 
SELECT a.*,b.lvl+1 FROM @t a /*原表*/ JOIN t b /*CTE*/ ON a.ParentDept = b.FullDept /*原表父项等于CTE子项*/
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S-IT IT S Peter 0
S-IT-CN CN S-IT Mary 1
S-IT-CN-SH SH S-IT-CN Jack 2

(3 行受影响)
*/
--->例2.查询部门主管为"Cici"的所有下级部门清单
;WITH t AS 
(
SELECT *,0 as lvl /*展开层次*/ FROM @t WHERE Supervisor ='Cici'
UNION ALL 
SELECT a.*,b.lvl+1 FROM @t a /*原表*/ JOIN t b /*CTE*/ ON a.ParentDept = b.FullDept /*原表父项等于CTE子项*/
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S 0 0 CiCi 0
S-FS FS S Jerry 1
S-FS-AP AP S-FS Colin 2
S-IT IT S Peter 1
S-IT-CN CN S-IT Mary 2
S-IT-CN-SH SH S-IT-CN Jack 3

(6 行受影响)
*/
-->2.使用CTE递归返回指定子项的父项查询：
--->例1.查询部门主管为"Jack"的所有上级部门清单
;WITH t AS 
(
SELECT *,0 as lvl /*展开层次*/ FROM @t WHERE Supervisor = 'Jack'
UNION ALL 
SELECT a.*,b.lvl+1 FROM @t a /*原表*/ JOIN t b /*CTE*/ ON b.ParentDept = a.FullDept /*CTE父项等于原表子项*/
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S 0 0 CiCi 3
S-IT IT S Peter 2
S-IT-CN CN S-IT Mary 1
S-IT-CN-SH SH S-IT-CN Jack 0

(4 行受影响)
*/
--->例2.查询部门主管为"Colin"的所有上级部门清单
;WITH t AS 
(
SELECT *,0 as lvl /*展开层次*/ FROM @t WHERE Supervisor = 'Colin'
UNION ALL 
SELECT a.*,b.lvl+1 FROM @t a /*原表*/ JOIN t b /*CTE*/ ON b.ParentDept = a.FullDept /*CTE父项等于原表子项*/
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S 0 0 CiCi 2
S-FS FS S Jerry 1
S-FS-AP AP S-FS Colin 0

(3 行受影响)
*/



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2010/01/15/5193800.aspx

[misterliwei]

select a.deptid, a.deptname, isuperdept = isnull(b.deptname, a.deptname)
from testdept a
left join testdept b
on a.superdept = b.deptid

[丰云]

在这个板块里看到太多不符合范式的table。。。。。。
人为的把一些原本很简单的事情搞复杂了。。。。。。
感叹下中国的软件业。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

[fuxiaoyang13]

kankan!!!!

[cyc123007512]

正确，谢谢！
[Quote=引用 2 楼 misterliwei 的回复:]
SQL codeselect a.deptid, a.deptname, isuperdept=isnull(b.deptname, a.deptname)from testdept aleftjoin testdept bon a.superdept= b.deptid
[/Quote]

[joypaker]

原来如此啊