declare @t table(机构 varchar(10),物料 varchar(10),单价 int,数量 int,金额 int)
insert into @t select
'总装', '扳手', 2, 2, 4 union all select
'总装', '起子', 2, 1, 2 union all select
'总装', '起子', 2, 1, 2 union all select
'注塑', '原料', 100, 2, 200 union all select
'注塑', '辅料', 120, 2, 240
select a.机构,a.物料,b.单价,b.数量,isnull(b.金额,a.金额) 金额 from
(select case when grouping(物料)=0 then 机构 else 机构+'合计' end 机构,物料,sum(金额) 金额 from @t group by 机构,物料 with rollup having grouping(机构)=0) a
left join
@t b on a.机构=b.机构 and a.物料=b.物料
declare @t table(机构 varchar(10),物料 varchar(10),单价 int,数量 int,金额 int)
insert into @t select
'总装', '扳手', 2, 2, 4 union all select
'总装', '起子', 2, 1, 2 union all select
'注塑', '原料', 100, 2, 200 union all select
'注塑', '辅料', 120, 2, 240
select a.机构,a.物料,b.单价,b.数量,a.金额 from
(select 机构,物料,sum(金额) 金额 from @t group by 机构,物料 with rollup having grouping(机构)=0) a
left join
@t b on a.机构=b.机构 and a.物料=b.物料
select [name]=case when grouping(name)=1 then '总合计' else [name] end
,type,price,number,[sum]=sum([sum])
from @ta
group by name,type,price,number with rollup
having grouping(name)=1 or (grouping(type)=0 and grouping(price)=0 and grouping(number)=0)
(1 行受影响)
(1 行受影响)
name type price number sum
---------- ----- ----------- ----------- -----------
注塑 辅料 120 2 240
注塑 原料 100 2 200
总合计 NULL NULL NULL 440