1098. 小众书籍（难度：中等）

题目描述

（通过次数12,263 | 提交次数25,815，通过率47.50%）

书籍表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 表的外键。

你需要写一段 SQL 命令，筛选出过去一年中订单总量少于10本的书籍。
注意：不考虑上架（available from）距今不满一个月 的书籍。并且假设今天是2019-06-23。

下面是样例输出结果：
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    |
+----------+---------+----------+---------------+
Result 表：
+-----------+--------------------+
| book_id   | name               |
+-----------+--------------------+
| 1         | "Kalila And Demna" |
| 2         | "28 Letters"       |
| 5         | "The Hunger Games" |
+-----------+--------------------+

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/unpopular-books

//测试数据
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);

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');

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');

解题思路

题目要求计算出满足下列条件的书籍：
条件1：上架（available from）距今满一个月；
条件2：最近一年订单总量少于10本；
分析上面的条件，条件1可以从Books表计算得出；条件2可以从Orders表计算得出。最后，再计算条件1和条件2结果的交集即可。
不过，因为Orders表中不存在销量为0的书籍，所以还需要对这部分书籍进行特殊处理。
另外，上面的计算步骤也比较复杂。
可以思考是否有相对简洁的写法。
比如，为减少子查询，可以先将Books表和Orders表进行左关联，形成一个大宽表。因为使用的是左关联，也不需要针对销量为0的书籍进行特殊考虑。
而且，如果对SQL的执行顺序很熟的话，不同的筛选条件，可以在合适的阶段进行筛选过滤。也许一句SQL就可以完全实现。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

select
    b.book_id,
    max(b.name) name
from Books b
left join Orders a
on a.book_id = b.book_id
and a.dispatch_date >= date_sub('2019-06-23',interval 1 year)
where b.available_from <= date_sub('2019-06-23',interval 1 month)
group by b.book_id
having coalesce(sum(a.quantity),0) < 10;