记录:产品 销售金额
---------------------------------
A 1000
A 1000
B 1000
A 1000
B 1000
C 100
A 1000
B 1000
A 1000
A 1000
D 110
B 1000
B 1000
B 1000
A 1000
A 1000统计目标:产品 销售额
---------------------
A 8000
B 6000
其他 210
合计 14210
统计,主导产品A,B销售情况,其他非A,B产品归为其他,最后是所有销售合计
---------------------------------
A 1000
A 1000
B 1000
A 1000
B 1000
C 100
A 1000
B 1000
A 1000
A 1000
D 110
B 1000
B 1000
B 1000
A 1000
A 1000统计目标:产品 销售额
---------------------
A 8000
B 6000
其他 210
合计 14210
统计,主导产品A,B销售情况,其他非A,B产品归为其他,最后是所有销售合计
select sum(case when 产品='A' then 销售金额 else 0 end) as A,
sum(case when 产品='B' then 销售金额 else 0 end) as B,
sum(case when 产品<>'A' and 产品<>'B' then 销售金额 else 0 end) as 其他,
sum(销售金额) as 合计
from tb
group by 产品
---------------------
再做行列互换
select 产品,sum(销售金额) from tab where 产品='A' or 产品='B' group by 产品 union all
select '其他',sum(销售金额) from tab where 产品<>'A' and 产品<>'B' union all
select '合计',sum(销售金额)from tab
select 产品=isnull(产品,'合计'),销售额 =sum(销售额) from
(select 产品=case when 产品<>'a' and 产品<>'b' then '其他' else 产品 end,销售额 from 表名)a
group by 产品 with rollup
select 产品=isnull(产品,'合计'),销售额 =sum(销售金额) from
(select 产品=case when 产品<>'a' and 产品<>'b' then '其他' else 产品 end,销售金额 from 表名)a
group by 产品 with rollup
drop table tb
go
create table tb(产品 varchar(100),销售金额 int)
insert into tb select 'A',1000
insert into tb select 'A',1000
insert into tb select 'B',1000
insert into tb select 'A',1000
insert into tb select 'B',1000insert into tb select 'C',100
insert into tb select 'A',1000
insert into tb select 'B',1000
insert into tb select 'A',1000
insert into tb select 'A',1000insert into tb select 'D',110
insert into tb select 'B',1000
insert into tb select 'B',1000
insert into tb select 'B',1000
insert into tb select 'A',1000
insert into tb select 'A',1000
go
select isnull(产品,'合计') as 产品,sum(销售金额) as 销售金额
from (
select case when 产品 in('a','b') then 产品 else '其他' end as 产品,销售金额
from tb) t
group by 产品
with rollupA 8000
B 6000
其他 210
合计 14210
union all
select 产品,sum(销售金额) as 销售额 from t where 产品='B'
union all
select 产品 as 其他 ,sum(销售金额) as 销售额 from t where 产品 not in('A','B')
union all
select 产品 as 合计 ,sum(销售金额) as 销售额 from t
不知道正确不??
select isnull(虚字段,合计),sum(销售金额)
from
(select 虚字段=(case when 产品<>'A' and 产品<>'B' then '其他' else 产品 end),销售金额
from tb) a
group by 虚字段
with rollup
drop table tb
go
create table tb(产品 varchar(100),销售金额 int)
insert into tb select 'A',1000
insert into tb select 'A',1000
insert into tb select 'B',1000
insert into tb select 'A',1000
insert into tb select 'B',1000insert into tb select 'C',100
insert into tb select 'A',1000
insert into tb select 'B',1000
insert into tb select 'A',1000
insert into tb select 'A',1000insert into tb select 'D',110
insert into tb select 'B',1000
insert into tb select 'B',1000
insert into tb select 'B',1000
insert into tb select 'A',1000
insert into tb select 'A',1000
goSELECT * FROM TBSELECT 产品,SUM(销售金额)AS 销售额 FROM TB WHERE 产品 IN ('A','B') GROUP BY 产品
UNION ALL
SELECT 产品='其他',SUM(销售金额)AS 销售额 FROM TB WHERE 产品 NOT IN ('A','B')
UNION ALL
SELECT 产品='合计',SUM(销售金额)AS 销售额 FROM TB
A 8000
B 6000
其他 210
合计 14210
if object_id('tempdb.dbo.#') is not null drop table #
create table #(产品 varchar(8), 销售金额 int)
insert into #
select 'A', 1000 union all
select 'A', 1000 union all
select 'B', 1000 union all
select 'A', 1000 union all
select 'B', 1000 union all
select 'C', 100 union all
select 'A', 1000 union all
select 'B', 1000 union all
select 'A', 1000 union all
select 'A', 1000 union all
select 'D', 110 union all
select 'B', 1000 union all
select 'B', 1000 union all
select 'B', 1000 union all
select 'A', 1000 union all
select 'A', 1000select
产品 = isnull(case when 产品 = 'A' or 产品 = 'B' then '其他' else 产品 end, '合计'),
销售额 = sum(销售金额)
from # group by
case when 产品 = 'A' or 产品 = 'B' then '其他' else 产品 end
with rollup/*
产品 销售额
-------- -----------
A 8000
B 6000
其他 210
合计 14210
*/
产品 = isnull(case when 产品 = 'A' or 产品 = 'B' then '其他' else 产品 end, '合计'),
销售额 = sum(销售金额)
from # group by
case when 产品 = 'A' or 产品 = 'B' then '其他' else 产品 end
with rollup
if object_id('tempdb.dbo.#') is not null drop table #
create table #(产品 varchar(8), 销售金额 int)
insert into #
select 'A', 1000 union all
select 'A', 1000 union all
select 'B', 1000 union all
select 'A', 1000 union all
select 'B', 1000 union all
select 'C', 100 union all
select 'A', 1000 union all
select 'B', 1000 union all
select 'A', 1000 union all
select 'A', 1000 union all
select 'D', 110 union all
select 'B', 1000 union all
select 'B', 1000 union all
select 'B', 1000 union all
select 'A', 1000 union all
select 'A', 1000select
产品 = isnull(case when 产品 <> 'A' and 产品 <> 'B' then '其他' else 产品 end, '合计'),
销售额 = sum(销售金额)
from # group by
case when 产品 <> 'A' and 产品 <> 'B' then '其他' else 产品 end
with rollup
/* 这个结果才是产品 销售额
-------- -----------
A 8000
B 6000
其他 210
合计 14210
*/
*/
drop table test
create table test(产品 varchar(10), 销售金额 int)
insert into test select 'A', 1000
insert into test select 'A', 1000
insert into test select 'B', 1000
insert into test select 'A', 1000
insert into test select 'B', 1000
insert into test select 'C', 100
insert into test select 'A', 1000
insert into test select 'B', 1000
insert into test select 'A', 1000
insert into test select 'A', 1000
insert into test select 'D', 110
insert into test select 'B', 1000
insert into test select 'B', 1000
insert into test select 'B', 1000
insert into test select 'A', 1000
insert into test select 'A', 1000
select '产品'=case when 产品='a' then 'a' when 产品='b' then 'b' when 产品 not in ('a','b') then '其他' end,'销售金额'=sum(销售金额) from test group by case when 产品='a' then 'a' when 产品='b' then 'b' when 产品 not in ('a','b') then '其他' end
-------------------------------------
产品 销售金额
---- -----------
a 8000
b 6000
其他 210(所影响的行数为 3 行)