下面为一个商品购买记录表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