力扣1098(MySQL)-小众书籍（中等）

题目：

书籍表 Books：

book_id 是这个表的主键

订单表 Orders：

order_id 是这个表的主键。
book_id 是 Books 表的外键。

问题
你需要写一段 SQL 命令，筛选出过去一年中订单总量少于10本的书籍。

注意：不考虑上架（available from）距今不满一个月的书籍。并且假设今天是 2019-06-23 。

示例
Books 表：

Orders 表：

Result 表：

建表语句：

1 Create table If Not Exists Books_1098 (book_id int, name varchar(50), available_from date);
2 Create table If Not Exists Orders_1098 (order_id int, book_id int, quantity int, dispatch_date date);
3 Truncate table Books_1098;
4 insert into Books_1098 (book_id, name, available_from) values ('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');
5 Truncate table Orders_1098;
6 insert into Orders_1098 (order_id, book_id, quantity, dispatch_date) values ('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');

解题思路：

方法一：

①先筛选出Orders 表过去一年的出售数大于10的book_id

1 select book_id
2 from orders_1098
3 where dispatch_date between '2018-06-23' and '2019-06-23'
4 group by book_id
5 having sum(quantity) > 10

②再去books表中筛选出出版日期大于1个月且不在上一步筛选出的book_id 的book_id 和 name

1 select book_id,name
2 from books_1098
3 where book_id not in (
4     select book_id
5     from orders_1098
6     where dispatch_date between '2018-06-23' and '2019-06-23'
7     group by book_id
8     having sum(quantity) > 10
9     ) and available_from <= '2019-05-23'

方法二：

用on 筛选出orders表中订单时间小于一年的数据进行左连接books表，然后where筛选出出版时间大于一个月的数据，再以book_id进行分组，使用having 筛选出出售数量小于10，最后select出book_id和name。

注意:

因为on去筛选，没有订单的至少返回null；而不符合where条件的，连null都不会保留。所以不满一个月应放在where后，过去一年中应放在on后。

1 select a.book_id,name
2 from books_1098 a
3 left join orders_1098 b
4 on a.book_id = b.book_id and datediff('2019-06-23',dispatch_date) <= 365
5 where datediff('2019-06-23',available_from) >= 30
6 group by a.book_id
7 having ifnull(sum(quantity),0) < 10