这样的分组汇总如何来写?

[xuehf]

产品销售情况表tblA
fitemid fqty famount
10 20.3 20
10 30 90
20 20 35
20 30 59
20 2 6
10 3 7
其中fitemid 为产品内部ID ,产品表如下 tblItem
fitemid fparentid fnumber fname fdepth
1 0 cp 成品 1
2 1 cp.001 库存商品 2
20 2 cp.001.1 库存商品A 3
10 2 cp.001.2 库存商品B 3
表结构如下:根结点:cp 根结点下面子结点cp.001 子结点cp.001 下面又有两个子结点
cp.001.1 cp.001.2

现希望得到如下结果(按fitemid汇总):
fNumber fname fqty famount
cp.001.1 库存商品A 58.3 117
cp.001.2 库存商品B 52 100
小计cp.001 库存商品 110.3 217
总计cp 成品 110.3 217
请问如何实现?

[子陌红尘]

--生成测试数据
create table tblA(fitemid int,fqty numeric(5,1),famount int)
insert into tblA select 10,20.3,20
insert into tblA select 10,30 ,90
insert into tblA select 20,20 ,35
insert into tblA select 20,30 ,59
insert into tblA select 20,2 ,6
insert into tblA select 10,3 ,7
create table tblItem(fitemid int,fparentid int,fnumber varchar(20),fname varchar(20),fdepth int)
insert into tblItem select 1 ,0,'cp ','成品 ',1
insert into tblItem select 2 ,1,'cp.001 ','库存商品 ',2
insert into tblItem select 20,2,'cp.001.1','库存商品A',3
insert into tblItem select 10,2,'cp.001.2','库存商品B',3
go

--创建用户定义函数
create function f_ischild(@fitemidA int,@fitemidB int)
returns int
as
begin
if @fitemidA=@fitemidB
return 1

declare @fitemidC int
select @fitemidC=fparentid from tblItem where fitemid=@fitemidB
while @@rowcount<>0
begin
if @fitemidA=@fitemidC
return 1
set @fitemidB=@fitemidC
select @fitemidC=fparentid from tblItem where fitemid=@fitemidB
end
return 0
end
go


--执行统计处理过程
select
fnumber=(case a.fdepth
when 1 then '总计'+a.fnumber
when 2 then '小计'+a.fnumber
else a.fnumber
end),
a.fname,
fqty = sum(b.fqty),
famount = sum(b.famount)
from
tblItem a
left join
tblA b
on
dbo.f_ischild(a.fitemid,b.fitemid)=1
group by
a.fnumber,a.fname,a.fdepth
order by
a.fdepth desc,a.fnumber
go

--输出结果
/*
cp.001.1 库存商品A 52.0 100
cp.001.2 库存商品B 53.3 117
小计cp.001 库存商品 105.3 217
总计cp 成品 105.3 217
*/


--删除测试环境
drop function f_ischild
drop table tblItem,tblA
go

[funsuzhou]

--测试数据：
create table Test_tblA(fitemid int,fqty decimal(28,2),famount decimal(28,2))
insert into Test_tblA
select 10, 20.3, 20 union all
select 10, 30, 90 union all
select 20, 20, 35 union all
select 20, 30, 59 union all
select 20, 2, 6 union all
select 10, 3, 7 union all
select 30, 5, 8 union all
select 40, 6, 9

create table Test_tblItem(fitemid int,fparentid int,fnumber varchar(20),fname varchar(30),fdepth int)
insert into Test_tblItem
select 1, 0, 'cp', '成品', 1 union all
select 2, 1, 'cp.001', '库存商品1', 2 union all
select 20, 2, 'cp.001.1', '库存商品A', 3 union all
select 10, 2, 'cp.001.2', '库存商品B', 3 union all
select 3, 1, 'cp.002', '库存商品2', 3 union all
select 30, 3, 'cp.002.1', '库存商品C', 3 union all
select 40, 3, 'cp.002.2', '库存商品D', 3


--创建2个自定义函数：
create function dbo.SumFqty(@fnumber varchar(20))
returns decimal(28,2)
as
begin
declare @re decimal(28,2)
select @re=sum(t.fqty)from(
select b.fnumber,b.fname,a.fqty,a.famount from Test_tblItem b left join Test_tblA a on b.fitemid=a.fitemid
)t
where charindex(@fnumber,t.fnumber)<>0
return @re
end

create function dbo.SumAmount(@fnumber varchar(20))
returns decimal(28,2)
as
begin
declare @re decimal(28,2)
select @re=sum(t.famount)from(
select b.fnumber,b.fname,a.fqty,a.famount from Test_tblItem b left join Test_tblA a on b.fitemid=a.fitemid
)t
where charindex(@fnumber,t.fnumber)<>0
return @re
end


--执行语句：
select fnumber=case when len(fnumber)>6 then t.fnumber when fname='成品'then '总计'+t.fnumber else '小计'+t.fnumber end,
t.fname,
fqty=dbo.SumFqty(t.fnumber),
famount=dbo.SumAmount(t.fnumber)from (
select b.fnumber,b.fname,a.fqty,a.famount from Test_tblItem b left join Test_tblA a on b.fitemid=a.fitemid
)t group by t.fnumber,t.fname


--删除测试表和自定义函数：
drop table Test_tblA,Test_tblItem
drop function dbo.SumFqty,dbo.SumAmount



/*
fnumber fname fqty famount
------------------------ ------------------------------ ------------------------------ ------------------------------
总计cp 成品 116.30 234.00
小计cp.001 库存商品1 105.30 217.00
cp.001.1 库存商品A 52.00 100.00
cp.001.2 库存商品B 53.30 117.00
小计cp.002 库存商品2 11.00 17.00
cp.002.1 库存商品C 5.00 8.00
cp.002.2 库存商品D 6.00 9.00

（所影响的行数为 7 行）
*/

[funsuzhou]

楼主，我是按照小计的fnumber按6位计算，我想这个应该是固定的，如果不是6位请相应改正之！

[mislrb]

mark