1098. 小众书籍-CSDN博客

本文链接：https://blog.csdn.net/CYJ1844/article/details/143083991

力扣题目跳转（1098. 小众书籍 - 力扣（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" |
+-----------+--------------------+

case 1 的建表语句。

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
where datediff('2019-06-23',available_from) > 30;

select
    *
from Orders
where dispatch_date between '2018-06-23' and '2019-06-23';

二我们在上面的基础上进行左连接，以 books 为主表，连接字段为 t1.book_id = t2.book_id

with tmp1 as
(select
    *
from Books
where datediff('2019-06-23',available_from) > 30)
,tmp2 as
(select
    *
from Orders
where dispatch_date between '2018-06-23' and '2019-06-23')
select *
from tmp1 t1
left join tmp2 t2 on t1.book_id = t2.book_id;

三然后我们在基础上进行数据的删选。

with tmp1 as

(select

*

from Books

where datediff('2019-06-23',available_from) > 30)

,tmp2 as

(select

*

from Orders

where dispatch_date between '2018-06-23' and '2019-06-23')

select t1.book_id,

name

from tmp1 t1

left join tmp2 t2 on t1.book_id = t2.book_id

group by t1.book_id, name

having ifnull(sum(quantity),0) < 10;