po完才發現昨天joedenkidd大大已經有PO更好的解決方法
獻醜了
練習一下
select tmpA.date , tmpeA.count_a , tmpeB.count_b from
(select date,count(value) as count_a from table where value='a' group by date)
as tmpA
inner join
(select date count(value) as count_b from table where value='b' group by date)
as tmpB
on tmpA.date =tmpB.date
結果如下
date count_a count_b