34,593
社区成员
发帖
与我相关
我的任务
分享
select
月份,
max( case when 产品='水笔' then 产品出售个数 end) as [水笔出售个数],
max( case when 产品='水笔' then 产品出售个数 end) as [圆珠笔出售个数],
max( case when 产品='水笔' then 产品出售个数 end) as[钢笔出售个数],
sum(产品出售金额) as [产品出售总金额],
sum(产品结算金额) as [产品出售总结算金额]
from
tb
group by
月份
use tempdb;
/*
create table tb
(
月份 nvarchar(10) not null,
产品 nvarchar(10) not null,
产品出售个数 int not null,
产品出售金额 int not null,
产品结算金额 int not null
);
insert into tb([月份],[产品],[产品出售个数],[产品出售金额],[产品结算金额])
values
('201101','水笔',8,80,70),
('201101','圆珠笔',6,40,35),
('201101','钢笔',10,90,88),
('201102','水笔',10,90,60),
('201103','圆珠笔',7,60,38),
('201104','钢笔',13,110,109);
*/
--语句1
select tb.[月份],
SUM(case when tb.[产品] = '水笔' then tb.[产品出售个数] else 0 end) as [水笔出售个数],
SUM(case when tb.[产品] = '圆珠笔' then tb.[产品出售个数] else 0 end) as [圆珠笔出售个数],
SUM(case when tb.[产品] = '钢笔' then tb.[产品出售个数] else 0 end) as [钢笔出售个数],
SUM(tb.[产品出售金额]) as [产品出售总金额],
SUM(tb.[产品结算金额]) as [产品出售总结算金额]
from tb
group by tb.[月份];
--语句2
select t.[月份],
SUM(case when t.[产品] = '水笔' then t.[产品出售个数] else 0 end) as [水笔出售个数],
SUM(case when t.[产品] = '圆珠笔' then t.[产品出售个数] else 0 end) as [圆珠笔出售个数],
SUM(case when t.[产品] = '钢笔' then t.[产品出售个数] else 0 end) as [钢笔出售个数],
SUM(t.[产品出售金额]) as [产品出售总金额],
SUM(t.[产品结算金额]) as [产品出售总结算金额]
from
(
select
case when tb.月份 <> '201101' then STUFF(tb.月份,6,1,'2') else tb.月份 end as [月份],
tb.产品,tb.产品出售个数,tb.产品出售金额,tb.产品结算金额
from tb
) as t
group by t.[月份];
select 月份, max( case when 产品='水笔' then 产品出售个数 end)[水笔出售个数],
max( case when 产品='水笔' then 产品出售个数 end)[圆珠笔出售个数],
max( case when 产品='水笔' then 产品出售个数 end)[钢笔出售个数],
sum(产品出售金额) [产品出售总金额],sum(产品结算金额) [产品出售总结算金额]
from tb group by 月份