SQL分组多列统计(GROUP BY后按条件分列统计) -转

今天在园子里看到个group by 分组多列统计的例子,转走给大家分享一下:

create table tests (year datetime year to year,type char(1),value int);
alter table tests alter colomn year int;

insert into tests values (2015,1,100);
insert into tests values (2015,2,200);
insert into tests values (2016,1,150);
insert into tests values (2016,2,300);
insert into tests values (2016,3,100);

YEAR TYPE VALUE
2015 1 100
2015 2 200
2016 1 150
2016 2 300
2016 3 100

 

 

 

 

 

转为:

YEAR TYPE1 TYPE2 TYPE3
2015 100 200 0
2016 150 300 100

 

 

 

 

这时候我们除了用到GROUP BY之外还需要CASE WHEN,SQL如下:

SELECT year,
 SUM(CASE WHEN type=1 THEN value ELSE 0 END) as type1,
 SUM(CASE WHEN type=2 THEN value ELSE 0 END) as type2,
 SUM(CASE WHEN type=3 THEN value ELSE 0 END) as type3,
 FROM table_test GROUP BY year

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。