Question

sql 递归查询顶级父类

CREATE TABLE [dbo].[Category](
[CategoryID] [int] NOT NULL,
[ParentID] [int] NOT NULL,
)
GO
INSERT [dbo].[Category] ([CategoryID], [ParentID]) VALUES (1, 0)
INSERT [dbo].[Category] ([CategoryID], [ParentID]) VALUES (2, 0)
INSERT [dbo].[Category] ([CategoryID], [ParentID]) VALUES (3, 1)
INSERT [dbo].[Category] ([CategoryID], [ParentID]) VALUES (4, 2)
INSERT [dbo].[Category] ([CategoryID], [ParentID]) VALUES (5, 4)
INSERT [dbo].[Category] ([CategoryID], [ParentID]) VALUES (6, 3)
INSERT [dbo].[Category] ([CategoryID], [ParentID]) VALUES (7, 5)
INSERT [dbo].[Category] ([CategoryID], [ParentID]) VALUES (8, 6)
INSERT [dbo].[Category] ([CategoryID], [ParentID]) VALUES (9, 8)
go
ParentID=0 表明是顶级父类，最后结果中 ParentID应该是0或1或2
最好考虑一下性能（数据很大）
需求：
计算出categoryID中ParentID对应顶级父类的categoryID（顶级父类的ParentID用自身的categoryID替代）
（当父类的ParentID=0时，表示是顶级数据）
！！！找父类的父类。。。，一直找到父类的ParentID=0才停止！！！
select categoryID,ParentID from tmp(计算出的结果集)

1，1
2，2
3，1
4，2
5，2
6，1
7，2
8，1
9，1

若以下回答无法解决问题，邀请你更新回答

Answer 1

是说要根据给定的CategoryID，查询出顶级父类的CategoryID？

追问

是呀

追答

下面这段代码是用循环的方式，查找顶级父类的id

declare @id int;
set @id=9;
 
declare @pid int; -- parent id
set @pid=-1;
if (exists(select 1 from [dbo].[Category] where CategoryID=@id))
  select @pid=isnull(ParentID,-1) from [dbo].[Category] where CategoryID=@id;
while (@pid!=-1)
begin
  set @id=@pid;
 if (exists(select 1 from [dbo].[Category] where CategoryID=@id))
   select @pid=isnull(ParentID,-1) from [dbo].[Category] where CategoryID=@id;
 else
  set @pid=-1;
end
select @id;

得到的结果是0。

 

当然，这样的方法效率不是很高。如果数据量很大，又有大量的查询顶级父类的需求，可以考虑在表中增加一个字段，专门保存本类型的顶级父类。这样会增加insert和update时候的工作量(需要让本类型的顶级父类等于父类的顶级父类)，但是可以大大提高查询时的效率。看你怎么权衡了。

追问

我要的结果是个列表，像这样的
select categoryID,ParentID from tmp(计算出的结果集)

1，0
2，0
3，1
4，2
5，2
6，1
7，2
8，1
9，1

追答

declare @t table(CategoryID int,ParentID1 int,ParentID2 int,IsEnd int);
insert into @t
select CategoryID,CategoryID,ParentID,0 from Category;
declare @n int;
set @n=10;   --设置最大循环次数，避免死循环
while (exists(select 1 from @t where IsEnd=0) and @n>=1)
begin
 set @n=@n-1;
 update @t
 set ParentID1=case when b.CategoryID is not null then a.ParentID2 else a.ParentID1 end,
     ParentID2=case when b.CategoryID is not null then b.ParentID else a.ParentID2 end,
   IsEnd=case when b.CategoryID is not null then 0 else 1 end
 from @t a 
 left join Category b on a.ParentID2=b.CategoryID
 where a.IsEnd=0;
end
if exists(select 1 from @t where IsEnd=0)
 select '已超过最大循环次数'
else
 select CategoryID,ParentID1 from @t

结果为：