需要统计的数据如下,表名为SOME_TYPE:
TYPE | SOCRE |
AA | 1 |
AA | 1 |
AA | 2 |
AA | 2 |
BB | 3 |
BB | 3 |
BB | 2 |
理想的统计结果如下:
TYPE | COUNT_1 | COUNT_2 | COUNT_3 |
AA | 2 | 2 | 0 |
BB | 0 | 1 | 2 |
比较好用的SQL如下:
select type,
sum(case when type=1 then 1 else 0 end) as count_1,
sum(case when type=2 then 1 else 0 end) as count_2,
sum(case when type=3 then 1 else 0 end) as count_3
from some_type group by type;