題目
Table: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| customer_id | int |
| order_date | date |
| item_id | varchar |
| quantity | int |
+---------------+---------+
(ordered_id, item_id) is the primary key for this table.
This table contains information of the orders placed.
order_date is the date when item_id was ordered by the customer with id customer_id.
Table: Items
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| item_id | varchar |
| item_name | varchar |
| item_category | varchar |
+---------------------+---------+
item_id is the primary key for this table.
item_name is the name of the item.
item_category is the category of the item.
You are the business owner and would like to obtain a sales report for category items and day of the week.
Write an SQL query to report how many units in each category have been ordered on each day of the week.
Return the result table ordered by category.
The query result format is in the following example:
Orders table:
+------------+--------------+-------------+--------------+-------------+
| 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 table:
+------------+----------------+---------------+
| 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 table:
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 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 |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
On Monday (2020-06-01, 2020-06-08) were sold a total of 20 units (10 + 10) in the category Book (ids: 1, 2).
On Tuesday (2020-06-02) were sold a total of 5 units in the category Book (ids: 1, 2).
On Wednesday (2020-06-03) were sold a total of 5 units in the category Phone (ids: 3, 4).
On Thursday (2020-06-04) were sold a total of 1 unit in the category Phone (ids: 3, 4).
On Friday (2020-06-05) were sold 10 units in the category Book (ids: 1, 2) and 5 units in Glasses (ids: 5).
On Saturday there are no items sold.
On Sunday (2020-06-14, 2020-06-21) were sold a total of 10 units (5 +5) in the category Phone (ids: 3, 4).
There are no sales of T-Shirt.
創(chuàng)建數(shù)據(jù)
CREATE TABLE Orders5(
order_id INT,
customer_id INT,
order_date DATE,
item_id VARCHAR(20),
quantity INT );
CREATE TABLE Items2(
item_id VARCHAR(20),
item_name VARCHAR(20),
item_category VARCHAR(20));
INSERT INTO Orders5 VALUE(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);
INSERT INTO Items2 VALUE(1, 'LC Alg. Book', 'Book'),
(2, 'LC DB. Book', 'Book'),
(3, 'LC SmarthPhone', 'Phone'),
(4, 'LC SmarthPhone', 'Phone'),
(5, 'LC SmartGlass', 'Glasses'),
(6, 'LC T-Shirt XL', 'T-Shirt');
解答
兩表連接 然后把時間轉(zhuǎn)化為星期 對星期和類別分組統(tǒng)計數(shù)量
然后就是轉(zhuǎn)寬
先對Orders將日期轉(zhuǎn)為星期
SELECT DAYNAME(O.`order_date`),O.`item_id`, O.`quantity`
FROM Orders5 AS O;
與Items進行連接
SELECT tmp.week, tmp.quantity, I.`item_category`
FROM (SELECT DAYNAME(O.`order_date`) AS `week`,O.`item_id`, O.`quantity`
FROM Orders5 AS O) AS tmp
JOIN Items2 AS I
ON I.`item_id` = tmp.`item_id`;
分組統(tǒng)計總數(shù)
SELECT tmp.week, I.`item_category`, SUM(tmp.quantity)
FROM (SELECT DAYNAME(O.`order_date`) AS `week`,O.`item_id`, O.`quantity`
FROM Orders5 AS O) AS tmp
JOIN Items2 AS I
ON I.`item_id` = tmp.`item_id`
GROUP BY tmp.week, I.`item_category`
ORDER BY I.`item_category`, tmp.week;
然后就與第618題類似
SELECT A.item_category,
MAX(IF(A.week = 'Monday', num, 0)) AS Monday,
MAX(IF(A.week = 'Tuesday', num, 0)) AS Tuesday,
MAX(IF(A.week = 'Wednesday', num, 0)) AS Wednesday,
MAX(IF(A.week = 'Thursday', num, 0)) AS Thursday,
MAX(IF(A.week = 'Friday', num, 0)) AS Friday,
MAX(IF(A.week = 'Saturday', num, 0)) AS Saturday,
MAX(IF(A.week = 'Sunday', num, 0)) AS Sunday
FROM (SELECT tmp.week, I.`item_category`, SUM(tmp.quantity) AS num
FROM (SELECT DAYNAME(O.`order_date`) AS `week`,O.`item_id`, O.`quantity`
FROM Orders5 AS O) AS tmp
JOIN Items2 AS I
ON I.`item_id` = tmp.`item_id`
GROUP BY tmp.week, I.`item_category`
ORDER BY I.`item_category`, tmp.week) AS A
GROUP BY A.item_category;
但是好像少了都為0的T-shirt行 將連接方式改為右連接即可
SELECT A.item_category,
MAX(IF(A.week = 'Monday', num, 0)) AS Monday,
MAX(IF(A.week = 'Tuesday', num, 0)) AS Tuesday,
MAX(IF(A.week = 'Wednesday', num, 0)) AS Wednesday,
MAX(IF(A.week = 'Thursday', num, 0)) AS Thursday,
MAX(IF(A.week = 'Friday', num, 0)) AS Friday,
MAX(IF(A.week = 'Saturday', num, 0)) AS Saturday,
MAX(IF(A.week = 'Sunday', num, 0)) AS Sunday
FROM (SELECT tmp.week, I.`item_category`, SUM(tmp.quantity) AS num
FROM (SELECT DAYNAME(O.`order_date`) AS `week`,O.`item_id`, O.`quantity`
FROM Orders5 AS O) AS tmp
RIGHT JOIN Items2 AS I
ON I.`item_id` = tmp.`item_id`
GROUP BY tmp.week, I.`item_category`
ORDER BY I.`item_category`, tmp.week) AS A
GROUP BY A.item_category;
