书籍表 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" | +-----------+--------------------+
解题思路
左联结
一开始左联结了books表,但是想了想Orders中的每个book_id都能在Books表中找到对应的,反过来就不一定了,有的书可能没卖出去过。
还有quantitiy…一开始看成quality,搞得我去SUM(order_id)发现怎么都做不对…真的是低级错误了。
# Write your MySQL query statement below
SELECT A.book_id,A.name
FROM books AS A
LEFT JOIN Orders AS B
ON A.book_id=B.book_id AND B.dispatch_date>'2018-06-23'
WHERE A.available_from<'2019-05-23'
GROUP BY A.book_id
HAVING SUM(B.quantity)<10 OR SUM(B.quantity)IS NULL