MySQL练手题--周内每天销售情况(困难)
一、准备工作
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');表：
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 是商品的类别你是企业主，想要获得分类商品和周内每天的销售报告。
编写解决方案，报告 周内每天 每个商品类别下订购了多少单位。
返回结果表单 按商品类别排序 。
结果格式如下例所示。
示例 1：
输入： 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 | +------------+----------------+---------------+ 输出： +------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ | 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 类别的商品
二、分析
1. 先使用函数将数据中的日期分成周，这个使用函数date+format（date，‘%W’）完成； select item_id, if(date_format(order_date,'%W')= 'Monday',quantity,0) Monday, if(date_format(order_date,'%W')= 'Tuesday',quantity,0) Tuesday, if(date_format(order_date,'%W')= 'Wednesday',quantity,0) Wednesday, if(date_format(order_date,'%W')= 'Thursday',quantity,0) Thursday, if(date_format(order_date,'%W')= 'Friday', quantity,0) Friday, if(date_format(order_date,'%W')= 'Saturday',quantity,0) Saturday, if(date_format(order_date,'%W')= 'Sunday', quantity,0) Sunday from Orders；item_id用来与商品表链接
2.链接上面查出的数据和item表，对商品类型分组聚合周每周内的销售情况，；
with t as ( select item_id, if(date_format(order_date,'%W')= 'Monday',quantity,0) Monday, if(date_format(order_date,'%W')= 'Tuesday',quantity,0) Tuesday, if(date_format(order_date,'%W')= 'Wednesday',quantity,0) Wednesday, if(date_format(order_date,'%W')= 'Thursday',quantity,0) Thursday, if(date_format(order_date,'%W')= 'Friday', quantity,0) Friday, if(date_format(order_date,'%W')= 'Saturday',quantity,0) Saturday, if(date_format(order_date,'%W')= 'Sunday', quantity,0) Sunday from Orders ) select item_category Category, sum(Monday) Monday, sum(Tuesday) Tuesday, sum(Wednesday) Wednesday, sum(Thursday) Thursday, sum(Friday) Friday, sum(Saturday) Saturday, sum(Sunday) Sunday from items i left join t on t.item_id = i.item_id group by item_category
3.在第二步查出的数据中有null值，添加判断条件将null值换为0，另外对商品类型进行排序得出结果；
三、实现
with t as (
select
item_id,
if(date_format(order_date,'%W')= 'Monday',quantity,0) Monday,
if(date_format(order_date,'%W')= 'Tuesday',quantity,0) Tuesday,
if(date_format(order_date,'%W')= 'Wednesday',quantity,0) Wednesday,
if(date_format(order_date,'%W')= 'Thursday',quantity,0) Thursday,
if(date_format(order_date,'%W')= 'Friday', quantity,0) Friday,
if(date_format(order_date,'%W')= 'Saturday',quantity,0) Saturday,
if(date_format(order_date,'%W')= 'Sunday', quantity,0) Sunday
from Orders
), t1 as (
select item_category Category,
sum(Monday) Monday,
sum(Tuesday) Tuesday,
sum(Wednesday) Wednesday,
sum(Thursday) Thursday,
sum(Friday) Friday,
sum(Saturday) Saturday,
sum(Sunday) Sunday
from items i left join t on t.item_id = i.item_id group by item_category
)
select
Category,
if(Monday is null,0,Monday) Monday,
if(Tuesday is null,0,Tuesday) Tuesday,
if(Wednesday is null,0,Wednesday) Wednesday,
if(Thursday is null,0,Thursday) Thursday,
if(Friday is null,0,Friday) Friday,
if(Saturday is null,0,Saturday) Saturday,
if(Sunday is null,0,Sunday) Sunday
from t1 order by Category
;四、总结
因为输入的是两张表，但题目主要要求的是将商品的周内每天的商品报告，需要对表进行开列，但由于题目给到的是日期，想要转成周内每天需要使用date+format（date，‘%W’）转换，完成之后再连接商品表对商品类型分组，对每个星期几求和，将含有null值的转为0，最后对类型分组，得出最后结果；
原文地址：https://blog.csdn.net/weixin_66919047/article/details/142424652
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！