求一个SQL查询语句写法

Question

--数据库是sqlserver2000declare @t table(id int,dept varchar(50),parentid int)
insert @t
select 1,'集团',null
union all
select 101,'北京分公司',1
union all
select 102,'上海分公司',1
union all
select 103,'朝阳分部',101
union all
select 104,'浦东分部',102
select * from @t--求下表id        deptdetail
1        集团
101        集团-北京分公司
102        集团-上海分公司
103        集团-北京分公司-朝阳分部
104        集团-上海分公司-浦东分部

Answer 1

--生成测试数据 
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 iddrop 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                 深圳市,罗湖区,福田区,宝安区,西乡镇,龙华镇,松岗镇
004  天河区     004                                         天河区
005  罗湖区     005                                         罗湖区
006  福田区     006                                         福田区
007  宝安区     007,008,009,010                             宝安区,西乡镇,龙华镇,松岗镇
008  西乡镇     008                                         西乡镇
009  龙华镇     009                                         龙华镇
010  松岗镇     010                                         松岗镇(10 行受影响)
*/drop table tb

Answer 2

--数据库是sqlserver2000
--判断一下最大级是多少，据此来设定父子关联的层数，根据目前的数据是三层
declare @t table(id int,dept varchar(50),parentid int)
insert @t
select 1,'集团',null
union all
select 101,'北京分公司',1
union all
select 102,'上海分公司',1
union all
select 103,'朝阳分部',101
union all
select 104,'浦东分部',102
select isnull(c.dept+'-','')+isnull(b.dept+'-','')+isnull(a.dept+'','')
from @t a 
left join @t b on a.parentid=b.id
left join @t c on b.parentid=c.id/*
集团
集团-北京分公司
集团-上海分公司
集团-北京分公司-朝阳分部
集团-上海分公司-浦东分部(5 行受影响)
*/
--求下表