1. 题目描述

书籍表 Books:

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| book_id        | int     |
| name           | varchar |
| available_from | date    |
+----------------+---------+

book_id 是这个表的主键(具有唯一值的列), name 是书名, available_from 上架时间

订单表 Orders:

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| order_id       | int     |
| book_id        | int     |
| quantity       | int     |
| dispatch_date  | date    |
+----------------+---------+

order_id 是这个表的主键(具有唯一值的列) book_id 是 Books 表的外键(reference 列) quantity 是书籍数量 dispatch_date 订单日期

编写解决方案, 筛选出过去一年中订单总量少于 10 本的书籍, 并且不考虑上架距今销售不满一个月的书籍, 假设今天是 2019-06-23 返回结果表无顺序要求

2. 测试用例

输入: 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" |
+-----------+--------------------+

3. 解题思路

查询近一年销售数量≥10本的book_id, 对book_id聚合查询统计销售数量

select book_id
from Orders
where dispatch_date >= '2018-06-23'
group by book_id
having sum(quantity) >= 10

查询结果

+-------+
|book_id|
+-------+
|4      |
+-------+

查询上架时间不满一个月书籍available_from < '2019-05-23’, 且近一年销售数量<10本的数据book_id not in (步骤 1 查询的结果)

select book_id, name
from Books
where available_from < '2019-05-23'
  and book_id not in (select book_id
                      from Orders
                      where dispatch_date >= '2018-06-23'
                      group by book_id
                      having sum(quantity) >= 10);

查询结果

+-------+----------------+
|book_id|name            |
+-------+----------------+
|1      |Kalila And Demna|
|2      |28 Letters      |
|5      |The Hunger Games|
+-------+----------------+

LeetCode--1098. 小众书籍

1. 题目描述

2. 测试用例

3. 解题思路