# Write your MySQL query statement below
1098. 小众书籍
书籍表 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" | +-----------+--------------------+
原站题解
mysql 解法, 执行用时: 553 ms, 内存消耗: 0 B, 提交时间: 2023-10-16 09:51:50
# Write your MySQL query statement below select b.book_id, name from books b left join orders o on b.book_id = o.book_id and dispatch_date >= '2018-06-23' where available_from < '2019-05-23' group by b.book_id having ifnull(sum(quantity), 0) < 10;