116，SQL训练之，力扣，1098. 小众书籍

• 学习：知识的初次邂逅
• 复习：知识的温故知新
• 练习：知识的实践应用

目录

一，原题力扣链接

二，题干

三，建表语句

四，分析

五，SQL解答

六，验证

七，知识点总结

---

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

书籍表 Books：

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| book_id        | int     |
| name           | varchar |
| available_from | date    |
+----------------+---------+
book_id 是这个表的主键（具有唯一值的列）。

订单表 Orders：

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| order_id       | int     |
| book_id        | int     |
| quantity       | int     |
| dispatch_date  | date    |
+----------------+---------+
order_id 是这个表的主键（具有唯一值的列）。
book_id  是 Books 表的外键（reference 列）。

编写解决方案，筛选出过去一年中订单总量 少于 10 本 的 书籍，并且 不考虑 上架距今销售 不满一个月 的书籍 。假设今天是 2019-06-23 。

返回结果表 无顺序要求 。

结果格式如下所示。

示例 1：

输入：
Books 表：
+---------+--------------------+----------------+
| book_id | name               | available_from |
+---------+--------------------+----------------+
| 1       | "Kalila And Demna" | 2010-01-01     |
| 2       | "28 Letters"       | 2012-05-12     |
| 3       | "The Hobbit"       | 2019-06-10     |
| 4       | "13 Reasons Why"   | 2019-06-01     |
| 5       | "The Hunger Games" | 2008-09-21     |
+---------+--------------------+----------------+
Orders 表：
+----------+---------+----------+---------------+
| order_id | book_id | quantity | dispatch_date |
+----------+---------+----------+---------------+
| 1        | 1       | 2        | 2018-07-26    |
| 2        | 1       | 1        | 2018-11-05    |
| 3        | 3       | 8        | 2019-06-11    |
| 4        | 4       | 6        | 2019-06-05    |
| 5        | 4       | 5        | 2019-06-20    |
| 6        | 5       | 9        | 2009-02-02    |
| 7        | 5       | 8        | 2010-04-13    |
+----------+---------+----------+---------------+
输出：
+-----------+--------------------+
| book_id   | name               |
+-----------+--------------------+
| 1         | "Kalila And Demna" |
| 2         | "28 Letters"       |
| 5         | "The Hunger Games" |
+-----------+--------------------+

三，建表语句

Create table If Not Exists Books (book_id int, name varchar(50), available_from date);
Create table If Not Exists Orders (order_id int, book_id int, quantity int, dispatch_date date);
Truncate table Books;
insert into Books (book_id, name, available_from) values ('1', 'Kalila And Demna', '2010-01-01');
insert into Books (book_id, name, available_from) values ('2', '28 Letters', '2012-05-12');
insert into Books (book_id, name, available_from) values ('3', 'The Hobbit', '2019-06-10');;
insert into Books (book_id, name, available_from) values ('4', '13 Reasons Why', '2019-06-01');
insert into Books (book_id, name, available_from) values ('5', 'The Hunger Games', '2008-09-21');
Truncate table Orders;
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('1', '1', '2', '2018-07-26');
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('2', '1', '1', '2018-11-05');
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('3', '3', '8', '2019-06-11');
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('4', '4', '6', '2019-06-05');
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('5', '4', '5', '2019-06-20');
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('6', '5', '9', '2009-02-02');
insert into Orders (order_id, book_id, quantity, dispatch_date) values ('7', '5', '8', '2010-04-13');


select * from books;
select * from Orders;

四，分析

 

表格大法 先过滤在左连接 最后在分组过滤

第一步：过滤掉books表中上架时间距离如今一个月的书籍；

第二步：过滤 掉 订单表中 非近一年的订单；

第三步：以书籍表左连接订单表 统计书籍的销售数量 而非订单的，书籍有可能没有销售； 第四步：null值转为0

第五步，分组聚合过滤

第六步：映射指定的列 并输出；

第一步：过滤掉books表中上架时间距离如今一个月的书籍；

在mysql中：

第二步：过滤 掉 订单表中 非近一年的订单；

在mysql中：

第三步：以书籍表左连接订单表 统计书籍的销售数量 而非订单的，书籍有可能没有销售；

在mysql中：

第四步：null值转为0

在mysql中：

第五步，分组聚合过滤

在mysql中：

第六步：映射指定的列 并输出；

五，SQL解答

with
    t1 as (       # 过滤掉 非近一年的的销售订单  提取过滤 利于查询
    select
        order_id, book_id, quantity, dispatch_date
    from Orders where dispatch_date > date_add('2019-6-23',interval -1 year)
)
#    select * from t1;
   ,t2 as (
    select * from Books where available_from < date_add('2019-6-23',interval -30 day )
)
#     select * from t2;
,t3 as (     # 左连接两个表  左连接 假如有的书籍没有被销售呢？
    select
        t2.book_id, name, available_from,t1.order_id, quantity, dispatch_date
    from t2 left join t1 on t2.book_id=t1.book_id
)
#     select * from t3;
,t4 as (  # 数量如果是null 就是0
    select
        book_id, name, available_from,  order_id, ifnull(quantity,0) as quantity,
        dispatch_date
    from t3
)
# select * from t4;
select  # 以书籍id 和书名分组 sum数量 过滤除数量小于10 的书籍和书名
        book_id, name
from t4
group by book_id,name
having  sum(quantity) <10;

六，验证

七，知识点总结

• 时间差函数的运用  date_add 前一年
• 时间差函数的运用 上一个月
• 左连接的运用
• null值转为0的运用
• 分组聚合+过滤的运用

• 学习：知识的初次邂逅
• 复习：知识的温故知新
• 练习：知识的实践应用