挑战：用最简单的语句实现一个较复杂的多级数据分类汇总。

[zhhfy]

现有一表（tb）如下：
id name parentid
1 a 0
2 b 0
3 c 1
4 d 1
5 e 2
6 f 2
7 g 3
8 h 3
另一张表(tb1）为：
id num
7 10
8 11
6 10
5 11
4 10

最后输出的结果应为(注意保持记录的顺序)：
id name num
1 a 31
3 c 1
7 g 10
8 h 11
4 d 10
2 b 21
5 e 10
6 f 11

问题：如何实现以上的输出？ 条件：语句尽量简单，可以用游标（但最好不用），能够适应更多级次类别的汇总.

[子陌红尘]

--生成测试数据
create table t1(id int,name char(1),parentid int)
insert into t1 select 1,'a',0
insert into t1 select 2,'b',0
insert into t1 select 3,'c',1
insert into t1 select 4,'d',1
insert into t1 select 5,'e',2
insert into t1 select 6,'f',2
insert into t1 select 7,'g',3
insert into t1 select 8,'h',3

create table t2(id int,num int)
insert into t2 select 7,10
insert into t2 select 8,11
insert into t2 select 6,10
insert into t2 select 5,11
insert into t2 select 4,10
go

--创建用户定义函数
create function f_getNum(@id int)
returns varchar(4000)
as
begin
declare @ret varchar(4000),@pid int
set @ret = right('0000'+rtrim(@id),4)
while exists(select 1 from t1 where id=@id and parentid!=0)
begin
select @pid=parentid from t1 where id=@id and parentid!=0
set @id = @pid
set @ret = right('0000'+rtrim(@id),4)+@ret
end
return @ret
end
go


--执行查询
select
a.id,
a.name,
num = sum(b.num)
from
t1 a
left join
t2 b
on
dbo.f_getNum(b.id) like dbo.f_getNum(a.id)+'%'
group by
a.id,a.name
order by
dbo.f_getNum(a.id)


--输出结果：
id name num
---- ------ -----
1 a 31
3 c 21
7 g 10
8 h 11
4 d 10
2 b 21
5 e 10
6 f 11

[子陌红尘]

以上用户定义函数适用于ID数据长度小于等于4，目录层次小于等于1000的情况，如果需要突破ID长度限制，请修改函数中如下代码，4代表长度。
set @ret = right('0000'+rtrim(@id),4)

[山林73]

试一试xml

[zhhfy]

很不错！

还有更好的方法吗？