MS SQL Server中

tb_city表結(jié)構(gòu)如下
id??????name??????parentid
1??????湖北??????0
2??????湖南??????0
3??????武漢??????1
4??????仙桃??????1
5??????長沙??????2
6??????蔡甸??????3

定義函數(shù)
create function c_tree(@initid int)/*定義函數(shù)c_tree，輸入?yún)?shù)為初始節(jié)點(diǎn)id*/
returns @t table(id int,name varchar(100),parentid int,lev int)/*定義表t用來存放取出的數(shù)據(jù)*/
begin
? declare @i int/*標(biāo)志遞歸級別*/
? set @i=1
? insert @t select id,name,parentid,@i from tb_city where id=@initid
? while @@rowcount<>0
? begin
? set @i=@i+1
? insert @t select a.id,a.name,a.parentid,@i from tb_city as a,@t as b
?where b.id=a.parentid and b.lev=@i-1
? end
return
end

執(zhí)行
使用函數(shù)
select * from c_tree(1) /*取湖北下面的子節(jié)點(diǎn)*/

Oracle中的實(shí)現(xiàn)

select *? from TB_CITY
/*此處可寫WHERE語句限制*/
start with?ID=1
connect by prior ID=PARENTID