无限级分类，如何获得每个ClassID的所属第一层的RootClassID

Question

表结果只有：ClassID  ClassName ParentClassID  Dept但是想用一条Sql语句同时显示出RootClassID，也就是每个分类的第一层父类的ClassID。形如：
ClassID  ClassName ParentClassID  Dept RootClassID
1 宠物   0    0    1
2 花     0    0    2
3 猫     1    1    1
4 狗     1    1    1
5 小猫   10   2  1
6 红花   2    2  2

Answer 1

create table test(ClassID int,ClassName varchar(4),ParentClassID int,Dept int)
insert into test select 1,'宠物',0,0
insert into test select 2,'花  ',0,0
insert into test select 3,'猫  ',1,1
insert into test select 4,'狗  ',1,1
insert into test select 5,'小猫',10,2
insert into test select 6,'红花',2,2
gocreate function f_getroot(@ClassID int)
returns int
as
begin
    select @ClassID=isnull(ClassID,null) from test where ClassID=@ClassID
    while @@rowcount<>0
    begin
        select @ClassID=ParentClassID from test where ClassID=@ClassID and ParentClassID!=0
    end
    return @ClassID
end
goselect *,dbo.f_getroot(ClassID) as RootClassID from test
go/*
ClassID     ClassName ParentClassID Dept        RootClassID 
----------- --------- ------------- ----------- ----------- 
1           宠物        0             0           1
2           花         0             0           2
3           猫         1             1           1
4           狗         1             1           1
5           小猫        10            2           10
6           红花        2             2           2
*/drop function f_getroot
drop table test
go