sql groupby分组多列统计

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

十步理解SQL：http://blog.jobbole.com/55086/