create table tb(产品编号 varchar(10),用户 varchar(10),区间 varchar(10),数量 decimal(18,2) ,品牌 varchar(10),包装类型 varchar(10))
insert into tb values('100001', '3000', '201203', 11.00 ,'Bud', 'BBT')
insert into tb values('100002', '3000', '201203', 11.00 ,'Bud', 'SBT')
insert into tb values('100002', '3000', '201203', 11.00 ,'Bud', 'SBT')
insert into tb values('100003', '3000', '201203', 11.00 ,'Bud', 'CAN')
insert into tb values('100004', '3000', '201203', 11.00 ,'Bud', 'OTH')
insert into tb values('100005', '3000', '201203', 11.00 ,'Htb', 'OTH')
go
--select 'Total' c1, 'total' c2 , sum(数量) c3 from tb
select * from
(
select 'Total' c1,包装类型 c2, sum(数量) c3 from tb group by 包装类型
union all
select isnull(品牌,'Total') c1, isnull(包装类型,'合计') c2 , sum(数量) c3 from tb group by 品牌 , 包装类型 with rollup
) t
order by case when c1 = 'total' then 1 else 2 end , c1 , (case when c2 = '合计' then 1 else 2 end)
drop table tb/*
c1 c2 c3
---------- ---------- ----------------------------------------
Total 合计 66.00
Total BBT 11.00
Total CAN 11.00
Total OTH 22.00
Total SBT 22.00
Bud 合计 55.00
Bud BBT 11.00
Bud CAN 11.00
Bud OTH 11.00
Bud SBT 22.00
Htb 合计 11.00
Htb OTH 11.00
(所影响的行数为 12 行)
*/