sqlµÝ¹é²éѯ

ÔÚ¹¤×÷ÖÐÓöµ½Ò»¸öÎÊÌâ,ÊÇÐèÒªsqlµÝ¹é²éѯµÄ.²»¶®,ÓÚÊǵ½csdnÉÏÈ¥ÎÊ,ÄÇÀïµÄЧÂÊÕæÊǷdz£¸ß,ÎÒÒÔǰҲûÔÚÉÏÃæÎʹýÎÊÌâ.
ÎÊÌâÃèÊö:
ÎÒÓÐÒ»¸ö±í½á¹¹ÈçÏÂ:
id upperid
1     2
3     2
4     1
5     3
¾ßÌå²ã´Î²»ÖªµÀ,ÎÒÏëÓõݹésqlÓï¾ä°ÑËùÓÐÊôÓÚij¸öupperidµÄÊý¾Ý,°üÀ¨ËüµÄ×ÓÊ÷,¶¼¶Á³öÈ¥£¬ÇëÎÊÓ¦¸Ã×ÓÔõôд?     
±ÈÈç˵ upperid =2
ÄÇôÏÈÕÒµ½1,3,È»ºóÔÙÓÉ1,3ÕÒµ½4,5
ʹÓÃsqlÓï¾äʵÏÖ
ÓÐÁ½Î»ÅóÓѶ¼¸øÁ˻ظ´:
fa_ge(鶴嘯¾ÅÌì)
Create table t(id int,upperid int)
insert into t
select 1,     2
union all select 3,     2
union all select 4,     1
union all select 5,     3
select * from t
create function aa(@upperid int)
returns @t table (id int,upperid int,level int)
as
begin
declare @i int
set @i=1
insert into @t
select *,@i from t where upperid=@upperid
while @@rowcount>0
begin
set @i=@i+1
insert into @t
select a.*,@i from t a left join @t b on a.upperid=b.id
where b.level=@i-1
end
return
end
select * from dbo.aa(1)
id          upperid     level      
----------- ----------- -----------
4           1           1
£¨ËùÓ°ÏìµÄÐÐÊýΪ 1 ÐУ©
select * from dbo.aa(2)
id          upperid     level      
----------- ----------- -----------
1           2           1
3           2           1
4           1     &nbs