declare @id varchar(2) set @id='a1'declare @t table(id varchar(2),idm varchar(8000),level int) declare @level int set @level=1 insert @t select id,id,@level from 表 where id=@id while @@rowcount>0 begin set @level=@level+1 insert @t select a.id,b.idm+','+id,@level from 表 a join @t b on a.ParentID=b.ID where b.level=@level-1 endselect * from @t
--上面的写错了字段名,改一下declare @id varchar(2) set @id='a1'declare @t table(id varchar(2),level int) declare @level int set @level=1 insert @t select id,@level from 表 where id=@id while @@rowcount>0 begin set @level=@level+1 insert @t select a.id,@level from 表 a join @t b on a.parent_id=b.ID where b.level=@level-1 end
--下面是数据测试--数据测试环境 declare @tb table(id varchar(2),parent_id varchar(2)) insert into @tb select 'a1','a' union all select 'b1','b' union all select 'a2','a1' union all select 'b2','b1' union all select 'a3','a2' union all select 'b3','b2'--查询处理 declare @id varchar(2) set @id='a1'declare @t table(id varchar(2),level int) declare @level int set @level=1 insert @t select id,@level from @tb where id=@id while @@rowcount>0 begin set @level=@level+1 insert @t select a.id,@level from @tb a join @t b on a.parent_id=b.ID where b.level=@level-1 end--显示结果 select * from @t--如果要显示全部信息 select a.* from @tb a join @t b on a.id=b.id/*--测试结果 id level ---- ----------- a1 1 a2 2 a3 3(所影响的行数为 3 行)id parent_id ---- --------- a1 a a2 a1 a3 a2(所影响的行数为 3 行) --*/
set @id='a1'declare @t table(id varchar(2),idm varchar(8000),level int)
declare @level int
set @level=1
insert @t select id,id,@level from 表 where id=@id
while @@rowcount>0
begin
set @level=@level+1
insert @t select a.id,b.idm+','+id,@level from 表 a join @t b on a.ParentID=b.ID
where b.level=@level-1
endselect * from @t
set @id='a1'declare @t table(id varchar(2),level int)
declare @level int
set @level=1
insert @t select id,@level from 表 where id=@id
while @@rowcount>0
begin
set @level=@level+1
insert @t select a.id,@level from 表 a join @t b on a.parent_id=b.ID
where b.level=@level-1
end
declare @tb table(id varchar(2),parent_id varchar(2))
insert into @tb
select 'a1','a'
union all select 'b1','b'
union all select 'a2','a1'
union all select 'b2','b1'
union all select 'a3','a2'
union all select 'b3','b2'--查询处理
declare @id varchar(2)
set @id='a1'declare @t table(id varchar(2),level int)
declare @level int
set @level=1
insert @t select id,@level from @tb where id=@id
while @@rowcount>0
begin
set @level=@level+1
insert @t select a.id,@level from @tb a join @t b on a.parent_id=b.ID
where b.level=@level-1
end--显示结果
select * from @t--如果要显示全部信息
select a.* from @tb a join @t b on a.id=b.id/*--测试结果
id level
---- -----------
a1 1
a2 2
a3 3(所影响的行数为 3 行)id parent_id
---- ---------
a1 a
a2 a1
a3 a2(所影响的行数为 3 行)
--*/
用三個TEMP表: 一個是目的表, 另二個是TEMP用.
在將一個TEMP插入到目標表時, 讀取該直接枝點插入到另一個TEMP表. 讀完第一個TEMP表後, 其枝點也已讀到了另一個TMEP表, 接下來清除第一個TEMP表, 將另一個TEMP表中的資料再讀入第一個TMEP. 這樣可以用循環操作.一直操作到沒有枝點了.
不論多少層, 都可取出