以表deparment为例
表结构为：
id 部门内码，
deptcode 部门编码，
parentdeptid 上级部门内码
使用t-sql：
代码如下 复制代码
with dep as
(
select id,deptcode,deptname from department where id=1
union all
select d.id,d.deptcode,d.deptname from dep
inner join department d on dep.id = d.parentdeptid
)
select * from dep 简单解释一下：with as属于sql server新特性cte（common table expression）的关键字，用来储存临时结果集。常用于代替子查询。本例中可以理解为，找出id=1的记录后，存放在临时表dept中，然后临时表和department进行内连接，找出它的子记录。子记录和第一条记录union后作为dept新的结果集继续进行内连接，找出新的子记录。
使用pl/sql：
代码如下 复制代码
select id,deptcode,deptname
from department
start with id = 1
connect by prior id = parentdeptid;
start with 表示从哪一行记录开始递归查询，即根节点
connect by 表示进行递归，后面跟递归条件
prior 表示前一条记录，表示上一条记录的id = 下一条记录的parentdeptid
比如上面的sql语句就可以理解为，以id 为1的记录为根节点，递归查询下一条记录的parentdeptid = 前一条记录的id
实例
代码如下 复制代码
id 是否为部门 部门名 上级id
1 y 部门0 1
31 y 部门1 1
32 n 张三 31
33 n 李二 31
34 y 部门2 31
35 n 王五 34
35 y 部门３ 34
36 n 小三 35
我想找询 id 值为 35 下级的所有人员包括下级部门的所有人员
代码如下 复制代码
--创建查询函数
create function f_id(
@id int --要查询的id
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select id,@l
from 表
where 上级id=@id
while @@rowcount> 0
begin
set @l=@l+1
insert @re select a.id,@l
from 表 a join @re b on a.上级id=b.id and b.level=@l-1
end
return
end
go
--调用函数进行查询
select a.* from 表 a join f_id(35) b on a.id=b.id
--测试数据
代码如下 复制代码
create table 表(id int,是否为部门 char(1),部门名 varchar(10),上级id int)
insert 表 select 1 , 'y ', '部门0 ' ,1
union all select 31, 'y ', '部门1 ' ,1
union all select 32, 'n ', '张三 ' ,31
union all select 33, 'n ', '李二 ' ,31
union all select 34, 'y ', '部门2 ',31
union all select 35, 'n ', '王五 ' ,34
union all select 35, 'y ', '部门3 ',34
union all select 36, 'n ', '小三 ' ,35
go
--创建查询函数
create function f_id(
@id int --要查询的id
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select id,@l
from 表
where 上级id=@id
while @@rowcount> 0
begin
set @l=@l+1
insert @re select a.id,@l
from 表 a join @re b on a.上级id=b.id and b.level=@l-1
end
return
end
go
--调用函数进行查询
select a.* from 表 a join f_id(35) b on a.id=b.id
go
--删除测试
drop table 表
drop function f_id
/*--测试结果
id 是否为部门 部门名 上级id
----------- ----- ---------- -----------
36 n 小三 35
（所影响的行数为 1 行）
--*/