mysql 统计场景语句

下面为一个商品购买记录表buys，字段id为主键，字段buy_time为购买时间(每次购买1件),字段uid为客户id，字段sid为商品id (只有3种商品)

请使用sql语句，按日期统计3种商品购买数(如下图)

解决方案

select da,sum(cnt1)as sid1,sum(cnt2)as sid2,sum(cnt3)as sid3 from (select date(buy_time) as da ,count(*) as cnt1,0 as cnt2,0 as cnt3 FROM `buys` where `sid`=1 group by da
union all
select date(buy_time) as da ,0 as cnt1,count(*) as cnt2,0 as cnt3 FROM `buys` where `sid`=2 group by da
union all
select date(buy_time) as da ,0 as cnt1,0 as cnt2,count(*)  as cnt3 FROM `buys` where `sid`=3 group by da
)a group by da