各品类周一到周日的销量：日期转星期再行转列

题意

订单表记录订单日期、商品和数量，商品表记录商品所属品类。输出每个品类在周一到周日每天（按星期汇总，不分哪一周）的销售数量，没有销量的填 0，按品类排序。

表结构

• Orders：order_id、customer_id、order_date、item_id、quantity；(order_id, item_id) 为主键
• Items：item_id（主键）、item_name、item_category

思路

先把订单日期转成星期几，再按品类分组，用七个条件求和把星期转成七列。以商品表为主表 LEFT JOIN 订单，没有任何订单的品类也会出现，各列为 0。MySQL 的 DAYOFWEEK 把周日记为 1、周一记为 2，要对应好。

参考解法

select item_category as category, 
sum(case when num = 2 then quantity else 0 end) as Monday,
sum(case when num = 3 then quantity else 0 end) as Tuesday,
sum(case when num = 4 then quantity else 0 end) as Wednesday,
sum(case when num = 5 then quantity else 0 end) as Thursday,
sum(case when num = 6 then quantity else 0 end) as Friday,
sum(case when num = 7 then quantity else 0 end) as Saturday,
sum(case when num = 1 then quantity else 0 end) as Sunday
from
(select item_category, quantity,dayofweek(order_date) as num from 
items i left join orders o 
on i.item_id=o.item_id) t
group by item_category
order by item_category