题目链接：点击打开链接
题目大意：略。
解题思路：注意 DAYOFWEEK 函数。
AC 代码
-- 解决方案(1) select distinct b.item_category as Category, ifnull(sum(case when dayofweek(a.order_date) = 2 then a.quantity end),0) Monday, ifnull(sum(case when dayofweek(a.order_date) = 3 then a.quantity end),0) Tuesday, ifnull(sum(case when dayofweek(a.order_date) = 4 then a.quantity end),0) Wednesday, ifnull(sum(case when dayofweek(a.order_date) = 5 then a.quantity end),0) Thursday, ifnull(sum(case when dayofweek(a.order_date) = 6 then a.quantity end),0) Friday, ifnull(sum(case when dayofweek(a.order_date) = 7 then a.quantity end),0) Saturday, ifnull(sum(case when dayofweek(a.order_date) = 1 then a.quantity end),0) Sunday from Orders a right join Items b on a.item_id = b.item_id group by Category order by Category -- 解决方案(2) WITH t AS(SELECT item_category, order_date, quantity, DAYOFWEEK(order_date) - 1 week FROM Orders JOIN Items USING(item_id)), tt AS(SELECT *, SUM(quantity) total FROM t GROUP BY item_category, week), ttt AS(SELECT item_category, IF(week = 1, total, 0) Monday, IF(week = 2, total, 0) Tuesday, IF(week = 3, total, 0) Wednesday, IF(week = 4, total, 0) Thursday, IF(week = 5, total, 0) Friday, IF(week = 6, total, 0) Saturday, IF(week = 0, total, 0) Sunday FROM tt) SELECT DISTINCT item_category Category, IFNULL(SUM(Monday), 0) Monday, IFNULL(SUM(Tuesday), 0) Tuesday, IFNULL(SUM(Wednesday), 0) Wednesday, IFNULL(SUM(Thursday), 0) Thursday, IFNULL(SUM(Friday), 0) Friday, IFNULL(SUM(Saturday), 0) Saturday, IFNULL(SUM(Sunday), 0) Sunday FROM ttt RIGHT JOIN (SELECT DISTINCT item_category FROM Items) tttt USING(item_category) GROUP BY item_category ORDER BY item_category
