数型表的编码及计算问题

Question

DECLARE @t table(childid varchar(10),parentid varchar(10),price numeric(18,4),amount numeric(18,4),rownum varchar(50))insert into @t values('a','0',null,3,null)
insert into @t values('a1','a',1,1,null)
insert into @t values('a2','a',1,1,null)
insert into @t values('a3','a',1,1,null)
insert into @t values('b','0',2,1,null)
insert into @t values('c','0',3,1,null)
insert into @t values('c1','c',1,3,null)
insert into @t values('c2','c',1,3,null)
insert into @t values('c3','c',null,3,null)
insert into @t values('c31','c3',100,1,null)
insert into @t values('c32','c3',200,1,null)如何得到
a 0 3 3.0000 a
a1 a 1.0000 1.0000 a,a1
a2 a 1.0000 1.0000 a,a2
a3 a 1.0000 1.0000 a,a3
b 0 2.0000 1.0000 b
c 0 306 1.0000 c
c1 c 1.0000 3.0000 c,c1
c2 c 1.0000 3.0000 c,c2
c3 c 300 3.0000 c,c3
c31 c3 100.0000          1.0000 c,c3,c31
c32 c3 200.0000          1.0000 c,c3,c32
…
注：父类展开的如(a1,a2,a3)多只是1个父类如(a)的组成结构,
父类单价是由子类计算得到,这个要如何计算，能否设置rownum这个编号(由父类编码+子身编码组成),想了很久，还是调试不成功，各位帮忙提点意见，谢谢

Answer 1

if object_id('tbTest') is not null
    drop table tbTest
if object_id('fnSumChildren') is not null
    drop function fnSumChildren
if object_id('fnJoinChildren') is not null
    drop function fnJoinChildren
GO
create table tbTest(childid varchar(10),parentid varchar(10),price numeric(18,4),amount numeric(18,4),rownum varchar(50))
insert into tbTest values('a','0',null,3,null)
insert into tbTest values('a1','a',1,1,null)
insert into tbTest values('a2','a',1,1,null)
insert into tbTest values('a3','a',1,1,null)
insert into tbTest values('b','0',2,1,null)
insert into tbTest values('c','0',3,1,null)
insert into tbTest values('c1','c',1,3,null)
insert into tbTest values('c2','c',1,3,null)
insert into tbTest values('c3','c',null,3,null)
insert into tbTest values('c31','c3',100,1,null)
insert into tbTest values('c32','c3',200,1,null)
GO

Answer 2

----创建单价计算函数
create function fnSumChildren(@id varchar(10),@parentid varchar(10),@price numeric(18,4))
returns numeric(18,4)
as
begin
    declare @SubSum numeric(18,4),@amount numeric(18,4)
    declare @TotalSum numeric(18,4),@pid varchar(10)
    declare @level int
    set @SubSum = 0   /*每层子节点的price*amount值*/
    set @TotalSum = 0 /*@id节点的总的price*amount值*/
    set @level = 0    /*@id节点的子结点层次数*/
    declare @t table(childid varchar(10),parentid varchar(10),amount numeric(18,4))
    insert @t select childid,parentid,amount from tbTest where childid = @id
    while @@rowcount > 0
    begin
        ----每层节点初始化
        set @SubSum = 0  
        set @amount = 0  
        set @level = @level + 1
        ----获得当前子节点的父节点的parentid和amount
        select @amount = isnull(b.amount,0),@pid = b.parentid from tbTest as a
        inner join @t as b on a.parentid = b.childid and 
        a.childid not in(select childid from @t)
        ----获得当前子节点的price*amount之和
        set @SubSum = isnull((select sum(a.price*a.amount) from tbTest as a
        inner join @t as b on a.parentid = b.childid and 
        a.childid not in(select childid from @t)),0)
        ----累加
        set @TotalSum = @TotalSum + isnull(@SubSum,0) * case 
        when @parentid <> @pid then isnull(@amount,0) else 1 end
        ----查找当前子结点的下级子节点
        insert @t select a.childid,a.parentid,a.amount from tbTest as a
        inner join @t as b on a.parentid = b.childid and 
        a.childid not in(select childid from @t) 
    end
    return  case when @level > 1 then @TotalSum else @price  end
end
GO
----创建字符连接函数
create function fnJoinChildren(@id varchar(10))
returns varchar(4000)
as
begin
    declare @t table(parentid varchar(10),childid varchar(10))
    insert @t select parentid ,childid from tbTest where childid = @id
    while @@rowcount > 0
    insert @t select a.parentid,a.childid from tbTest as a  
    inner join @t as b on a.childid = b.parentid 
    and a.parentid not in(select parentid from @t) 
    declare @str varchar(4000)
    set @str = ''
    select @str = childid + ',' + @str  from @t
    return reverse(stuff(reverse(@str),1,1,''))
end
GO
----查询
SELECT childid,parentid,
    dbo.fnSumChildren(childid,parentid,price) as price ,
    amount,
    dbo.fnJoinChildren(childid) as rownum
FROM tbTest----清除测试环境
drop table tbTest
drop function fnSumChildren,fnJoinChildren/*结果
childid    parentid   price                amount               rownum 
---------- ---------- -------------------- -------------------- -------
a          0          3.0000               3.0000               a
a1         a          1.0000               1.0000               a,a1
a2         a          1.0000               1.0000               a,a2
a3         a          1.0000               1.0000               a,a3
b          0          2.0000               1.0000               b
c          0          906.0000             1.0000               c
c1         c          1.0000               3.0000               c,c1
c2         c          1.0000               3.0000               c,c2
c3         c          300.0000             3.0000               c,c3
c31        c3         100.0000             1.0000               c,c3,c31
c32        c3         200.0000             1.0000               c,c3,c32
*/

Answer 3

请楼主把上面的二个回复连接起来,因为无法将帖子一次性发出来,只好拆开发(最近饱受CSDN如此的折磨!).