1098. Unpopular Books 难度：中等

1、题目描述

Write an SQL query that reports the books that have sold less than 10 copies in the last year, excluding books that have been available for less than 1 month from today. Assume today is 2019-06-23.

The query result format is in the following example:

Books table:

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 table:

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 table:

book_id	name
1	“Kalila And Demna”
2	“28 Letters”
5	“The Hunger Games”

来源：力扣（LeetCode）

2、解题思路

1# 增加子表，分别计算在2018年卖的数量

select book_id,sum(quantity) as qu
from Orders
where year(dispatch_date)=2018
group by book_id

2# Books表与上表联查，条件是上表卖的数小于10，并且与今天’2019-06-23’相差小于30
where ifnull(qu,0)<=10 and datediff('2019-06-23',available_from)>30

3、提交记录

select b.book_id,b.name
from Books b left join 

(select book_id,sum(quantity) as qu
from Orders
where year(dispatch_date)=2018
group by book_id)jude

on b.book_id=jude.book_id
where ifnull(qu,0)<=10 and datediff('2019-06-23',available_from)>30

系统出错，无法通过