1098. 小众书籍

Leetcode题目:LEFT JOIN

书籍表 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
Subscribe
提醒
0 评论
Inline Feedbacks
View all comments
0
在此留下你的评论x