日期:2014-05-18 浏览次数:20616 次
--生成测试数据 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_cid(@id varchar(10)) returns varchar(8000) as begin declare @i int , @ret varchar(8000) declare @t table(id varchar(10) , pid varchar(10) , level int) set @i = 1 insert into @t select id , pid , @i from tb where id = @id while @@rowcount <> 0 begin set @i = @i + 1 insert into @t select a.id , a.pid , @i from tb a , @t b where a.pid = b.id and b.level = @i - 1 end select @ret = isnull(@ret , '') + id + ',' from @t return left(@ret , len(@ret) - 1) end go --执行查询 select id , children = isnull(dbo.f_cid(id) , '') from tb group by id drop table tb drop function f_cid /* id children ---- --------------------------------------- 001 001,002,003,004,005,006,007,008,009,010 002 002,004 003 003,005,006,007,008,009,010 004 004 005 005 006 006 007 007,008,009,010 008 008 009 009 010 010*/ (所影响的行数为 10 行) 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 , cid = id from tb union all select t.id , cid = tb.id from t join tb on tb.pid = t.cid ) select id , cid = STUFF((SELECT ',' + rtrim(cid) FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '') from tb group by id order by id /* id cid ---- --------------------------------------- 001 001,002,003,005,006,007,008,009,010,004 002 002,004 003 003,005,006,007,008,009,010 004 004 005 005 006 006 007 007,008,009,010 008 008 009 009 010 010 (10 行受影响) */ ;with t as ( select id , name , cid = id , path = cast(name as nvarchar(100)) from tb union all select t.id , t.name , cid = tb.id , path = cast(tb.name as nvarchar(100)) from t join tb on tb.pid = t.cid ) select id , name , cid = STUFF((SELECT ',' + rtrim(cid) FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , ''), path = STUFF((SELECT ',' + path FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '') from tb group by id , name order by id /* id name cid path ---- ---------- ------------------------------------------- --------------------------------- 001 广东省 001,002,003,005,006,007,008,009,010,004 广东省,广州市,深圳市,罗湖区,福田区,宝安区,西乡镇,龙华镇,松岗镇,天河区 002 广州市 002,004 广州市,天河区 003 深圳市 003,005,006,007,008,009,010 深圳市,罗湖区,福田区,宝安区,西乡镇,