2021-05-13

小众书籍

需求：筛选出订单总量少于10本的书籍 。

展示效果：

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

Create table If Not Exists 46_Books (book_id int, name varchar(50), available_from date);
Create table If Not Exists 46_Orders (order_id int, book_id int, quantity int, dispatch_date date);
Truncate table 46_Books;
insert into 46_Books (book_id, name, available_from) values (1, 'Kalila And Demna', '2010-01-01');
insert into 46_Books (book_id, name, available_from) values (2, '28 Letters', '2012-05-12');
insert into 46_Books (book_id, name, available_from) values (3, 'The Hobbit', '2019-06-10');
insert into 46_Books (book_id, name, available_from) values (4, '13 Reasons Why', '2019-06-01');
insert into 46_Books (book_id, name, available_from) values (5, 'The Hunger Games', '2008-09-21');
Truncate table 46_Orders;
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (1, 1, 2, '2018-07-26');
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (2, 1, 1, '2018-11-05');
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (3, 3, 8, '2019-06-11');
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (4, 4, 6, '2019-06-05');
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (5, 4, 5, '2019-06-20');
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (6, 5, 9, '2009-02-02');
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (7, 5, 8, '2010-04-13');

最终SQL:

select
	book_id,
	t2.`name`,
	ord			
from
	46_Books b1		
join
	(SELECT
	t1.`name`,
	sum(count) ord
from
	(	
select 
	`NAME`,
	IFNULL(quantity,0) count
FROM
	46_Books b
 left JOIN
	46_Orders o
on
b.book_id = o.book_id) t1
GROUP BY
	`name`
ORDER BY
		ord) t2
on
		b1.`name` = t2.`name`
HAVING
		ord <= 10