根据日期求比赛的正负次数，sum(case ? when ? then else 0 end)的用法

**

根据日期求比赛的正负次数

**如图👇
1、这是开始的查询所有图

2、这是要得到的结果图

3、解法：
（一）这是开始自己想的：

select z.date ‘日期’,z.a ‘正’,f.b ‘负’ from (select date,count(state) ‘a’
from matchs where state=‘正’ group by date) as z left join ( select
date,count(state) ‘b’ from matchs where state=‘负’ group by date) as f
on z.date=f.date;

（二）后来找到了简便的方法：

select date as ‘日期’,sum(case state when ‘正’ then 1 else 0 end) ‘正’,
sum(case state when ‘负’ then 1 else 0 end) ‘负’ from matchs group by
date;

4、总结sum(case ? when ?