select date,count(case when value='a' then 1 else null end) as count_a,count(case when value='b' then 1 else null end) as count_b from table group by date
这样应该可以达到你要的结果

Thanks~!