select 客户=case when grouping(客户)=1 then '' else rtrim(客户) end,
产品=case when grouping(客户)=1 then '合计' else rtrim(产品) end,
销售=sum(销售),
累计销售=sum(累计销售)
from B1
group by 产品,客户 with rollup
having not(grouping(客户)=1 and grouping(产品)=1)
产品=case when grouping(客户)=1 then '合计' else rtrim(产品) end,
销售=sum(销售),
累计销售=sum(累计销售)
from B1
group by 产品,客户 with rollup
having not(grouping(客户)=1 and grouping(产品)=1)
(
select [ID]=(case 产品 when 'AES' then 1 when 'AOS' then 2 else 3 end),
*
from 表 where 产品='AES'
union
select [ID]=(case 产品 when 'AES' then 1 when 'AOS' then 2 else 3 end),
'','合计',sum(销售),sum(累计销售)
from 表 group by 产品
)t
order by ID
create table B1(客户 varchar(20),产品 varchar(20),销售 int,累计销售 int)
insert into B1 select 'AAA','AES',2,8
insert into B1 select 'BBB','AES',5,10
insert into B1 select 'CCC','AES',5,8
insert into B1 select 'DDD','AOS',2,2
insert into B1 select 'AAA','AOS',5,8
insert into B1 select 'BBB','AOS',4,5
insert into B1 select 'AAA','MES',2,2
insert into B1 select 'CCC','MES',4,8
insert into B1 select 'DDD','MES',4,6
--执行查询
select
客户 = (case when 客户 is null then '' else 客户 end),
产品 = (case when 客户 is null then '合计' else 产品 end),
销售,
累计销售
from
(select
客户,产品,销售=sum(销售),累计销售=sum(累计销售)
from
B1
group by
产品,客户
with rollup) a
where
产品 is not null
--输出结果
客户 产品 销售 累计销售
---- ---- ---- --------
AAA AES 2 8
BBB AES 5 10
CCC AES 5 8
合计 12 26
AAA AOS 5 8
BBB AOS 4 5
DDD AOS 2 2
合计 11 15
AAA MES 2 2
CCC MES 4 8
DDD MES 4 6
合计 10 16
(
[客户] varchar(10),
[产品] varchar(10),
[销售] int,
[累计销售] int
)
insert @tb
select 'AAA','AES',2,8 union
select 'BBB','AES',5,10 union
select 'CCC','AES',5,8 union
select 'DDD','AOS',2,2 union
select 'AAA','AOS',5,8 union
select 'BBB','AOS',4,5 union
select 'AAA','MES',2,2 union
select 'CCC','MES',4,8 union
select 'DDD','MES',4,6select * from
(
select [ID]=(case [产品] when 'AES' then 1 when 'AOS' then 2 else 3 end),
*
from @tb
union
select [ID]=(case [产品] when 'AES' then 1 when 'AOS' then 2 else 3 end),
'','合计',sum([销售]),sum([累计销售])
from @tb group by [产品]
)t
order by ID,[产品]--结果
/*
ID 客户 产品 销售 累计销售
----------- ---------- ---------- ----------- -----------
1 AAA AES 2 8
1 BBB AES 5 10
1 CCC AES 5 8
1 合计 12 26
2 AAA AOS 5 8
2 BBB AOS 4 5
2 DDD AOS 2 2
2 合计 11 15
3 AAA MES 2 2
3 CCC MES 4 8
3 DDD MES 4 6
3 合计 10 16(所影响的行数为 12 行)
*/
(客户 varchar(4),
产品 varchar(4),
销售 int,
累计销售 int)
insert b1
select 'AAA','AES',2,8
union select 'BBB','AES',5,10
union select 'CCC','AES',5,8
union select 'DDD','AOS',2,2
union select 'AAA','AOS',5,8
union select 'BBB','MES',4,5
union select 'AAA','MES',2,2
union select 'CCC','MES',4,8
union select 'DDD','MES',4,6
select *
from b1
select 客户,产品,sum(销售) as 销售,sum(累计销售) as 累计销售
from b1
group by 产品,客户 with rollup