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

Question

--生成测试数据
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

Answer 1

--测试数据：
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,     9create 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
endcreate 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 行）
*/

Answer 2

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