本文介绍了一种使用SQL存储过程实现递归查询的方法，通过创建表格存储组织结构，并利用存储过程递归地获取指定节点下的所有子节点及其层级信息。
create table A
(
id int,
upid int,
caption varchar(100)
)
insert A
select 1,0,'总经理办公室' union
select 2,1,'人事部' union
select 3,1,'业务部' union
select 4,3,'业务部(内)' union
select 5,3,'业务部(外)'
create proc Temp_proc(@ID int)
as
declare @T_levn int
declare @t1 table(id int,upid int,caption varchar(100),T_levn int)
set @T_levn=1
insert @t1 select A.*,@T_levn from A where A.id=@ID
while @@rowcount>0
begin
set @T_levn=@T_levn+1
insert @t1 select A.*,@T_levn from A where A.upid in (select id from @t1 where T_levn=@T_levn-1)
end
select * from @t1
exec Temp_proc 3
(
id int,
upid int,
caption varchar(100)
)
insert A
select 1,0,'总经理办公室' union
select 2,1,'人事部' union
select 3,1,'业务部' union
select 4,3,'业务部(内)' union
select 5,3,'业务部(外)'
create proc Temp_proc(@ID int)
as
declare @T_levn int
declare @t1 table(id int,upid int,caption varchar(100),T_levn int)
set @T_levn=1
insert @t1 select A.*,@T_levn from A where A.id=@ID
while @@rowcount>0
begin
set @T_levn=@T_levn+1
insert @t1 select A.*,@T_levn from A where A.upid in (select id from @t1 where T_levn=@T_levn-1)
end
select * from @t1
exec Temp_proc 3
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
