写个函数,参考以下 select * from tb create table tb(id int identity(1,1),pid int,name varchar(20)) insert tb select 0,'中国' union all select 0,'美国' union all select 0,'加拿大' union all select 1,'北京' union all select 1,'上海' union all select 1,'江苏' union all select 6,'苏州' union all select 7,'常熟' union all select 6,'南京' union all select 6,'无锡' union all select 2,'纽约' union all select 2,'旧金山' go--查询指定id的所有子 create function f_cid( @id int )returns @re table(id int,level int) as begin declare @l int set @l=0 insert @re select @id,@l while @@rowcount>0 begin set @l=@l+1 insert @re select a.id,@l from tb a,@re b where a.pid=b.id and b.level=@l-1 end /*--如果只显示最明细的子(下面没有子),则加上这个删除 delete a from @re a where exists( select 1 from tb where pid=a.id) --*/ return end go--调用(查询所有的子) select a.*,层次=b.level from tb a,f_cid(2)b where a.id=b.id go--查询指定id的所有父 create function f_pid( @id int )returns @re table(id int,level int) as begin declare @l int set @l=0 insert @re select pid,@l from tb where id=@id and pid<>0 while @@rowcount>0 begin set @l=@l+1 insert @re select a.pid,@l from tb a,@re b where a.id=b.id and b.level=@l-1 and a.pid<>0 end return end go--调用(查询所有的父) select a.* from tb a,f_pid(7)b where a.id=b.id go--删除测试 drop table tb drop function f_cid drop function f_pid
给个例子: -------------------------------------------- create table DINOSAURS ( OrderNo int primary KEY, OrderName varchar(30), PredecessorNo int NULL REFERENCES DINOSAURS (OrderNo) )insert into DINOSAURS values(1,'中国',1) insert into DINOSAURS values(2,'河南省',1) insert into DINOSAURS values(3,'郑州',2) insert into DINOSAURS values(4,'驻马店',2) insert into DINOSAURS values(5,'管城区',3) insert into DINOSAURS values(6,'中原区',3) insert into DINOSAURS values(7,'二七区',3) insert into DINOSAURS values(8,'芒山区',3) insert into DINOSAURS values(9,'金水区',3) insert into DINOSAURS values(10,'经八路',9) insert into DINOSAURS values(11,'红旗路',9) insert into DINOSAURS values(12,'文化路',9) insert into DINOSAURS values(13,'黄河路',9) ------------------------------------------------ 创建存储过程: ---------------------------------------------CREATE PROCEDURE dbo.treeFrame AScreate table #work(lvl int, OrderNo int) create table #DINOSAURS(seq int identity, lvl int, OrderNo int)declare @lvl int,@curr int select top 1 @lvl = 1,@curr = OrderNo from DINOSAURS where OrderNo = PredecessorNoinsert into #work (lvl, OrderNo) values(@lvl, @curr) while(@lvl > 0) begin print @lvl if exists(select * from #work where lvl = @lvl) begin select top 1 @curr = OrderNo from #work where lvl = @lvl
insert into #DINOSAURS(lvl, OrderNo) values(@lvl, @curr) delete #work where lvl = @lvl and OrderNo = @curr insert into #work select @lvl + 1, OrderNo from DINOSAURS where PredecessorNo = @curr and PredecessorNo <> OrderNo if(@@ROWCOUNT > 0) set @lvl = @lvl + 1 end else set @lvl = @lvl - 1 endselect i.OrderNo as OrderNo,REPLICATE(CHAR(9),d.lvl) + i.OrderName as OrderName, d.lvl as depth from #DINOSAURS d join DINOSAURS i on (d.OrderNo = i.OrderNo) order by seq ---------------------------------------------------------- 执行结果:exec treeFrame -------------------------------- OrederNo OrderName depth 1 中国 1 2 河南省 2 3 郑州 3 5 管城区 4 6 中原区 4 7 二七区 4 8 芒山区 4 9 金水区 4 10 经八路 5 11 红旗路 5 12 文化路 5 13 黄河路 5 4 驻马店 3
合作部
select * from tb
create table tb(id int identity(1,1),pid int,name varchar(20))
insert tb select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江苏'
union all select 6,'苏州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'无锡'
union all select 2,'纽约'
union all select 2,'旧金山'
go--查询指定id的所有子
create function f_cid(
@id int
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l
from tb a,@re b
where a.pid=b.id and b.level=@l-1
end
/*--如果只显示最明细的子(下面没有子),则加上这个删除
delete a from @re a
where exists(
select 1 from tb where pid=a.id)
--*/
return
end
go--调用(查询所有的子)
select a.*,层次=b.level from tb a,f_cid(2)b where a.id=b.id
go--查询指定id的所有父
create function f_pid(
@id int
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select pid,@l from tb where id=@id and pid<>0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.pid,@l
from tb a,@re b
where a.id=b.id and b.level=@l-1 and a.pid<>0
end
return
end
go--调用(查询所有的父)
select a.* from tb a,f_pid(7)b where a.id=b.id
go--删除测试
drop table tb
drop function f_cid
drop function f_pid
--------------------------------------------
create table DINOSAURS
(
OrderNo int primary KEY,
OrderName varchar(30),
PredecessorNo int NULL REFERENCES DINOSAURS (OrderNo)
)insert into DINOSAURS values(1,'中国',1)
insert into DINOSAURS values(2,'河南省',1)
insert into DINOSAURS values(3,'郑州',2)
insert into DINOSAURS values(4,'驻马店',2)
insert into DINOSAURS values(5,'管城区',3)
insert into DINOSAURS values(6,'中原区',3)
insert into DINOSAURS values(7,'二七区',3)
insert into DINOSAURS values(8,'芒山区',3)
insert into DINOSAURS values(9,'金水区',3)
insert into DINOSAURS values(10,'经八路',9)
insert into DINOSAURS values(11,'红旗路',9)
insert into DINOSAURS values(12,'文化路',9)
insert into DINOSAURS values(13,'黄河路',9)
------------------------------------------------
创建存储过程:
---------------------------------------------CREATE PROCEDURE dbo.treeFrame AScreate table #work(lvl int, OrderNo int)
create table #DINOSAURS(seq int identity, lvl int, OrderNo int)declare @lvl int,@curr int
select top 1 @lvl = 1,@curr = OrderNo from DINOSAURS where OrderNo = PredecessorNoinsert into #work (lvl, OrderNo) values(@lvl, @curr)
while(@lvl > 0) begin
print @lvl
if exists(select * from #work where lvl = @lvl) begin
select top 1 @curr = OrderNo from #work where lvl = @lvl
insert into #DINOSAURS(lvl, OrderNo) values(@lvl, @curr) delete #work where lvl = @lvl and OrderNo = @curr insert into #work select @lvl + 1, OrderNo from DINOSAURS
where PredecessorNo = @curr and PredecessorNo <> OrderNo if(@@ROWCOUNT > 0) set @lvl = @lvl + 1
end else
set @lvl = @lvl - 1
endselect i.OrderNo as OrderNo,REPLICATE(CHAR(9),d.lvl) + i.OrderName as OrderName, d.lvl as depth
from #DINOSAURS d join DINOSAURS i on (d.OrderNo = i.OrderNo)
order by seq
----------------------------------------------------------
执行结果:exec treeFrame
--------------------------------
OrederNo OrderName depth
1 中国 1
2 河南省 2
3 郑州 3
5 管城区 4
6 中原区 4
7 二七区 4
8 芒山区 4
9 金水区 4
10 经八路 5
11 红旗路 5
12 文化路 5
13 黄河路 5
4 驻马店 3