已知题目:
表测试数据如下:
create table C(Id int ,Namevarchar(20),Parent_id int)
insert into C values(1,'w',null)
insert into C values(3,'d',null)
insert into C values(5,'d',1)
insert into C values(6,'f',3)
insert into C values(7,'f',1)
insert into C values(8,'g',null)
insert into C values(9,'g',null)
insert into C values(10,'gd',6)
insert into C values(13,'gx',7)
方法一:
--网络上某人的方法
select * from
(select ID,Name,Parent_id,(case whenid>Parent_id then Parent_id else id end) flag from C) t
order by flag,id
方法二:
思路:将所有根节点提出 然后 获得根节点下的所有子节点,遍历根节点的记录。使用unionall 连接即可:
·首先创建函数GetSubNode_C(@parentid int):
create function GetSubNode_C(@parentid int)
returns @t table(Id int ,Namevarchar(20),Parent_id int,Level int)
As
Begin
declare @Level int --等级 根节点等级为1
set @Level=1
insert into @t selectId,Name,Parent_id,@Level from C where Id=@parentid
while @@rowcount>0 --如果至少有一条子节点
begin
set @Level=@Level+1
insert into @t selecta.Id,a.Name,a.Parent_id,@Level from C a,@t b
where a.Parent_id in (select Id from C whereId=b.ID)
andb.Level=@Level-1
end
return
End
·然后游标遍历所有根节点(parentidis null)的记录,拼装sql语句,最后执行
declare @sql varchar(8000)
set @sql=''
declare @Id int
declare cur cursor for
select id from C where parent_id is null
open cur
fetch next from cur into @Id
while @@fetch_status=0
begin
if len(@sql)=0
set@sql=@sql+'select * from dbo.GetSubNode_C('+cast(@Id as varchar(10))+')'
else
set@sql=@sql+' union all select * from dbo.GetSubNode_C('+cast(@Id asvarchar(10))+')'
fetch next from cur into @Id
end
close cur
deallocate cur
exec(@sql)
运行结果截图: