数型表的编码及计算问题
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)

如何得到
a033.0000a
a1a1.00001.0000a,a1
a2a1.00001.0000a,a2
a3a1.00001.0000a,a3
b02.00001.0000b
c03061.0000c
c1c1.00003.0000c,c1
c2c1.00003.0000c,c2
c3c3003.0000c,c3
c31c3100.0000 1.0000c,c3,c31
c32c3200.0000 1.0000c,c3,c32
…
注：父类展开的如(a1,a2,a3)多只是1个父类如(a)的组成结构,
父类单价是由子类计算得到,这个要如何计算，能否设置rownum这个编号(由父类编码+子身编码组成),想了很久，还是调试不成功，各位帮忙提点意见，谢谢

[解决办法]
c03061.0000c

--------------------
什是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
----创建字符连接函数

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
*/