Sqlserver父子节点查询 与 分类排序 已知题目: 表测试数据如下： create table C(Id int ,Name varchar(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 when id>Parent_id then Parent_id else id end) flag from C) t order by flag,id 方法二： 思路：将所有根节点提出 然后 获得根节点下的所有子节点，遍历根节点的记录。使用union all 连接即可： ·首先创建函数GetSubNode_C(@parentid int)： create function GetSubNode_C(@parentid int) returns @t table(Id int ,Name varchar(20),Parent_id int,Level int) As Begin declare @Level int --等级 根节点等级为1 set @Level=1 insert into @t select Id,Name,Parent_id,@Level from C where Id=@parentid while @@rowcount>0 --如果至少有一条子节点 begin set @Level=@Level+1 insert into @t select a.Id,a.Name,a.Parent_id,@Level from C a,@t b where a.Parent_id in (select Id from C where Id=b.ID) and b.Level=@Level-1 end return End ·然后游标遍历所有根节点(parentid is 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 as varchar(10))+')' fetch next from cur into @Id end close cur deallocate cur exec(@sql) 运行结果截图： 本文来源：https://www.wddqw.com/doc/262dff5e0b1c59eef8c7b438.html