1479.(Hard)周内每天的销售情况

表：Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| order_date    | date    | 
| item_id       | varchar |
| quantity      | int     |
+---------------+---------+
(order_id, item_id) 是该表主键
该表包含了订单信息
order_date 是id为 item_id 的商品被id为 customer_id 的消费者订购的日期.

表：Items

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| item_id             | varchar |
| item_name           | varchar |
| item_category       | varchar |
+---------------------+---------+
item_id 是该表主键
item_name 是商品的名字
item_category 是商品的类别

你是企业主，想要获得分类商品和周内每天的销售报告。

写一个SQL语句，报告 周内每天 每个商品类别下订购了多少单位。

返回结果表单 按商品类别排序 。

查询结果格式如下例所示：

Orders 表：
+------------+--------------+-------------+--------------+-------------+
| order_id   | customer_id  | order_date  | item_id      | quantity    |
+------------+--------------+-------------+--------------+-------------+
| 1          | 1            | 2020-06-01  | 1            | 10          |
| 2          | 1            | 2020-06-08  | 2            | 10          |
| 3          | 2            | 2020-06-02  | 1            | 5           |
| 4          | 3            | 2020-06-03  | 3            | 5           |
| 5          | 4            | 2020-06-04  | 4            | 1           |
| 6          | 4            | 2020-06-05  | 5            | 5           |
| 7          | 5            | 2020-06-05  | 1            | 10          |
| 8          | 5            | 2020-06-14  | 4            | 5           |
| 9          | 5            | 2020-06-21  | 3            | 5           |
+------------+--------------+-------------+--------------+-------------+

Items 表：
+------------+----------------+---------------+
| item_id    | item_name      | item_category |
+------------+----------------+---------------+
| 1          | LC Alg. Book   | Book          |
| 2          | LC DB. Book    | Book          |
| 3          | LC SmarthPhone | Phone         |
| 4          | LC Phone 2020  | Phone         |
| 5          | LC SmartGlass  | Glasses       |
| 6          | LC T-Shirt XL  | T-Shirt       |
+------------+----------------+---------------+

Result 表：
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Category   | Monday    | Tuesday   | Wednesday | Thursday  | Friday    | Saturday  | Sunday    |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Book       | 20        | 5         | 0         | 0         | 10        | 0         | 0         |
| Glasses    | 0         | 0         | 0         | 0         | 5         | 0         | 0         |
| Phone      | 0         | 0         | 5         | 1         | 0         | 0         | 10        |
| T-Shirt    | 0         | 0         | 0         | 0         | 0         | 0         | 0         |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
在周一(2020-06-01, 2020-06-08)，Book分类(ids: 1, 2)下，总共销售了20个单位(10 + 10)
在周二(2020-06-02)，Book分类(ids: 1, 2)下，总共销售了5个单位
在周三(2020-06-03)，Phone分类(ids: 3, 4)下，总共销售了5个单位
在周四(2020-06-04)，Phone分类(ids: 3, 4)下，总共销售了1个单位
在周五(2020-06-05)，Book分类(ids: 1, 2)下，总共销售了10个单位，Glasses分类(ids: 5)下，总共销售了5个单位
在周六, 没有商品销售
在周天(2020-06-14, 2020-06-21)，Phone分类(ids: 3, 4)下，总共销售了10个单位(5 + 5)
没有销售 T-Shirt 类别的商品

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/sales-by-day-of-the-week

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

Solution

SELECT item_category Category, 
    SUM(IF(WEEKDAY(order_date)=0,quantity,0))  Monday,
    SUM(IF(WEEKDAY(order_date)=1,quantity,0))  Tuesday,
    SUM(IF(WEEKDAY(order_date)=2,quantity,0))  Wednesday,
    SUM(IF(WEEKDAY(order_date)=3,quantity,0))  Thursday,
    SUM(IF(WEEKDAY(order_date)=4,quantity,0))  Friday,
    SUM(IF(WEEKDAY(order_date)=5,quantity,0))  Saturday,
    SUM(IF(WEEKDAY(order_date)=6,quantity,0))  Sunday
from Orders o RIGHT JOIN Items i on o.item_id = i.item_id
GROUP BY item_category
ORDER BY 1;

Table Schema

Create table If Not Exists Orders (order_id int, customer_id int, order_date date, item_id varchar(30), quantity int);
Create table If Not Exists Items (item_id varchar(30), item_name varchar(30), item_category varchar(30));
Truncate table Orders;
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('1', '1', '2020-06-01', '1', '10');
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('2', '1', '2020-06-08', '2', '10');
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('3', '2', '2020-06-02', '1', '5');
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('4', '3', '2020-06-03', '3', '5');
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('5', '4', '2020-06-04', '4', '1');
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('6', '4', '2020-06-05', '5', '5');
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('7', '5', '2020-06-05', '1', '10');
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('8', '5', '2020-06-14', '4', '5');
insert into Orders (order_id, customer_id, order_date, item_id, quantity) values ('9', '5', '2020-06-21', '3', '5');
Truncate table Items;
insert into Items (item_id, item_name, item_category) values ('1', 'LC Alg. Book', 'Book');
insert into Items (item_id, item_name, item_category) values ('2', 'LC DB. Book', 'Book');
insert into Items (item_id, item_name, item_category) values ('3', 'LC SmarthPhone', 'Phone');
insert into Items (item_id, item_name, item_category) values ('4', 'LC Phone 2020', 'Phone');
insert into Items (item_id, item_name, item_category) values ('5', 'LC SmartGlass', 'Glasses');
insert into Items (item_id, item_name, item_category) values ('6', 'LC T-Shirt XL', 'T-shirt');