SQL Tree解法

create table tb(id varchar(3) , pid varchar(3) , name varchar(10))insert into tb values('001' , null  , '廣東省')insert into tb values('002' , '001' , '廣州市')insert into tb values('003' , '001' , '深圳市')insert into tb values('004' , '002' , '天河區')insert into tb values('005' , '003' , '羅湖區')insert into tb values('006' , '003' , '福田區')insert into tb values('007' , '003' , '寶安區')insert into tb values('008' , '007' , '西鄉鎮')insert into tb values('009' , '007' , '龍華鎮')insert into tb values('010' , '007' , '松崗鎮')go--查詢各節點的父路徑函數(從父到子)create function f_pid1(@id varchar(3)) returns varchar(100)asbegin  declare @re_str as varchar(100)  set @re_str = ''  select @re_str = name from tb where id = @id  while exists (select 1 from tb where id = @id and pid is not null)    begin      select @id = b.id , @re_str = b.name + ',' + @re_str from tb a , tb b where a.id = @id and a.pid = b.id    end  return @re_strendgo--查詢各節點的父路徑函數(從子到父)create function f_pid2(@id varchar(3)) returns varchar(100)asbegin  declare @re_str as varchar(100)  set @re_str = ''  select @re_str = name from tb where id = @id  while exists (select 1 from tb where id = @id and pid is not null)    begin      select @id = b.id , @re_str = @re_str + ',' + b.name from tb a , tb b where a.id = @id and a.pid = b.id    end  return @re_strendgoselect * ,        dbo.f_pid1(id) [路徑(從父到子)] ,       dbo.f_pid2(id) [路徑(從子到父)]from tb order by iddrop function f_pid1 , f_pid2drop table tb/*id   pid  name    路徑(從父到子)               路徑(從子到父)              ---- ---- ------  ---------------------------  ----------------------------001  NULL 廣東省  廣東省                       廣東省002  001  廣州市  廣東省,廣州市                廣州市,廣東省003  001  深圳市  廣東省,深圳市                深圳市,廣東省004  002  天河區  廣東省,廣州市,天河區         天河區,廣州市,廣東省005  003  羅湖區  廣東省,深圳市,羅湖區         羅湖區,深圳市,廣東省006  003  福田區  廣東省,深圳市,福田區         福田區,深圳市,廣東省007  003  寶安區  廣東省,深圳市,寶安區         寶安區,深圳市,廣東省008  007  西鄉鎮  廣東省,深圳市,寶安區,西鄉鎮  西鄉鎮,寶安區,深圳市,廣東省009  007  龍華鎮  廣東省,深圳市,寶安區,龍華鎮  龍華鎮,寶安區,深圳市,廣東省010  007  松崗鎮  廣東省,深圳市,寶安區,松崗鎮  松崗鎮,寶安區,深圳市,廣東省（所影響的行數為 10 行）*/

/*標題：SQL SERVER 2005中查詢指定節點及其所有父節點的方法(字串形式顯示)作者：愛新覺羅·毓華(十八年風雨,守得冰山雪蓮花開) 時間：2010-02-02地點：新疆烏魯木齊*/create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))insert into tb values('001' , null  , N'廣東省')insert into tb values('002' , '001' , N'廣州市')insert into tb values('003' , '001' , N'深圳市')insert into tb values('004' , '002' , N'天河區')insert into tb values('005' , '003' , N'羅湖區')insert into tb values('006' , '003' , N'福田區')insert into tb values('007' , '003' , N'寶安區')insert into tb values('008' , '007' , N'西鄉鎮')insert into tb values('009' , '007' , N'龍華鎮')insert into tb values('010' , '007' , N'松崗鎮')go;with t as(    select id , pid = id from tb     union all    select t.id , pid = tb.pid from t inner join tb on t.pid = tb.id) select id ,        [路徑(從父到子)] = STUFF((SELECT ',' + pid FROM t WHERE id = tb.id order by t.id , t.pid FOR XML PATH('')) , 1 , 1 , ''),       [路徑(從子到父)] = STUFF((SELECT ',' + pid FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '')from tbgroup by idorder by id/*id   路徑(從父到子)   路徑(從子到父)---- ---------------  ---------------001  001              001002  001,002          002,001003  001,003          003,001004  001,002,004      004,002,001005  001,003,005      005,003,001006  001,003,006      006,003,001007  001,003,007      007,003,001008  001,003,007,008  008,007,003,001009  001,003,007,009  009,007,003,001010  001,003,007,010  010,007,003,001(10 行受影響)*/;with t as(    select id , name , pid = id , path = cast(name as nvarchar(100)) from tb     union all    select t.id , t.name , pid = tb.pid , path = cast(tb.name as nvarchar(100)) from t join tb on tb.id = t.pid )select id ,        name ,       [路徑(從父到子)_1] = pid1,        [路徑(從父到子)_2] = reverse(substring(reverse(path1) , charindex(',' , reverse(path1)) + 1 , len(path1))) ,       [路徑(從子到父)_1] = pid2,       [路徑(從子到父)_2] = substring(path2 , charindex(',' , path2) + 1 , len(path2)) from(select id , name ,       pid1 = STUFF((SELECT ',' + pid FROM t WHERE id = tb.id order by t.id , t.pid FOR XML PATH('')) , 1 , 1 , ''),       pid2 = STUFF((SELECT ',' + pid FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , ''),       path1 = STUFF((SELECT ',' + path FROM t WHERE id = tb.id order by t.id , t.pid FOR XML PATH('')) , 1 , 1 , ''),       path2 = STUFF((SELECT ',' + path FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '')from tbgroup by id , name) morder by id/*id   name    路徑(從父到子)_1  路徑(從父到子)_2             路徑(從子到父)_1  路徑(從子到父)_2---- ------  ----------------  ---------------------------  ----------------  ---------------------------001  廣東省  001               廣東省                       001               廣東省002  廣州市  001,002           廣東省,廣州市                002,001           廣州市,廣東省003  深圳市  001,003           廣東省,深圳市                003,001           深圳市,廣東省004  天河區  001,002,004       廣東省,廣州市,天河區         004,002,001       天河區,廣州市,廣東省005  羅湖區  001,003,005       廣東省,深圳市,羅湖區         005,003,001       羅湖區,深圳市,廣東省006  福田區  001,003,006       廣東省,深圳市,福田區         006,003,001       福田區,深圳市,廣東省007  寶安區  001,003,007       廣東省,深圳市,寶安區         007,003,001       寶安區,深圳市,廣東省008  西鄉鎮  001,003,007,008   廣東省,深圳市,寶安區,西鄉鎮  008,007,003,001   西鄉鎮,寶安區,深圳市,廣東省009  龍華鎮  001,003,007,009   廣東省,深圳市,寶安區,龍華鎮  009,007,003,001   龍華鎮,寶安區,深圳市,廣東省010  松崗鎮  001,003,007,010   廣東省,深圳市,寶安區,松崗鎮  010,007,003,001   松崗鎮,寶安區,深圳市,廣東省(10 行受影響)*/drop table tb