题目:
你需要写一段 SQL 命令,筛选出过去一年中订单总量 少于10本 的 书籍 。
注意:不考虑 上架(available from)距今 不满一个月 的书籍。并且 假设今天是 2019-06-23 。
样例数据:
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 |
Result 表:
book_id | name |
---|---|
1 | “Kalila And Demna” |
2 | “28 Letters” |
5 | “The Hunger Games” |
解题思路:
- 题目要求
- 计算过去一年内销量之和
- 少于10本
- 排除上市不到一个月的书籍
- 解答思路
使用排除法,排除不符合筛选条件的数据
难点
三个条件不在同一个表格中,难以同时筛选
Orders表格中的两个筛选条件(销售时间限制,销售数量限制)同时写,另外一个筛选条件(上市日期)分开写
答案1
select b.book_id,b.name
from Books b
where datediff('2019-06-23',b.available_from)>30
and b.book_id not in(
select book_id from Orders
where datediff('2019-06-23',dispatch_date ) <=365
group by book_id
having sum(quantity) >=10
)
答案2
select b1.book_id,b1.name
from Books b1
where (b1.book_id,b1.name) not in (select temp.book_id, temp.name from
(select b.book_id, b.name, sum(o.quantity) as quantity,b.available_from
from Books b
left join Orders o
on b.book_id = o.book_id
where (o.dispatch_date between "2018-06-23" and "2019-06-23")
group by b.book_id, b.name
having quantity>=10 or b.available_from > "2019-05-23") temp)
注:文章题目来源于leetcode(https://leetcode-cn.com/problems/unpopular-books/)