12、 有表TEST
ID FATHER CHIELD
1 A A1
2 A A2
3 A B
4 B B1
5 B B2
转成表
COLDE CLASS ISEND
A 1 0
B 2 0
A1 2 1
A2 2 1
B1 3 1
B2 3 1
ID FATHER CHIELD
1 A A1
2 A A2
3 A B
4 B B1
5 B B2
转成表
COLDE CLASS ISEND
A 1 0
B 2 0
A1 2 1
A2 2 1
B1 3 1
B2 3 1
ID int , FATHER nvarchar(10), CHIELD nvarchar(10))
insert into test
select
1, 'A', 'A1'
union select 2, 'A', 'A2'
union select 3, 'A', 'B'
union select 4, 'B', 'B1'
union select 5, 'B', 'B2' Go
Create table #a(COLDE nvarchar(10), CLASS int , ISEND int )
declare @I int
set @I=1
insert into #a select distinct FATHER,@I,0 from test where FATHER not in (select CHIELD from test )
while @@rowcount>0
begin
set @i=@i+1
insert into #a
select test.CHIELD ,@I ,(case when exists (select 1 from test b where test.CHIELD=b.FATHER) then 0 else 1 end )
from test inner join #a on test.FATHER=#a.COLDE where #a.CLASS=@i-1
endselect * from #adrop table #a
COLDE CLASS ISEND
---------- ----------- -----------
A 1 0
A1 2 1
A2 2 1
B 2 0
B1 3 1
B2 3 1(6 row(s) affected)
create table test1(id int,father varchar(10),chield varchar(10))
insert into test1 select 1,'A','A1'
insert into test1 select 2,'A','A2'
insert into test1 select 3,'A','B'
insert into test1 select 4,'B','B1'
insert into test1 select 5,'B','B2'create proc sp_test1()
as
declare @t table(father varchar(10),class int,isend int)
declare @class int
set @class=1
insert into @t select distinct father,@class,0 from test1 a where not exists(select 1 from test1 where chield=a.father)
while(@@rowcount>0)
begin
set @class=@class+1
insert into @t select a.chield,@class,(select case count(1) when 0 then 1 else 0 end from test1 where father=a.chield) from test1 a,@t b
where b.father=a.father and @class=b.class+1
end
select * from @t order by class,isendexec sp_test1
4#我的理解正确么?谢谢了
ID FATHER CHIELD
1 A A1
2 A A2
3 A B
4 B B1
5 B B2
转成表
COLDE CLASS ISEND
A 1 0
B 2 0
A1 2 1
A2 2 1
B1 3 1
B2 3 1 --------------
colde这个是列出所有节点
class是该节点的级别
isend是否该节点最后一层,0表示不是,1表示是。
或者这样理解:isend表示该节点是否存在子节点,如果存在,则为0,不存在则为1