数型表的编码及计算问题
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这个编号(由父类编码+子身编码组成),想了很久，还是调试不成功，各位帮忙提点意见，谢谢

------解决方案--------------------
c 0 306 1.0000 c

--------------------
為什麼是306?
------解决方案--------------------
恩，楼主以后细心点
------解决方案--------------------
----创建单价计算函数
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
----创建字符连接函