23 小众书籍

[astak16]

题目

筛选过去一年订单总量少于 10 本的书籍，不考虑上架不满一个月（时间：2019-06-23）的书籍。

create table books (
	book_id int,
	name varchar(255),
	available_from date
);

insert into books values
(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');

create table orders (
	order_id int,
	book_id int,
	quantity int,
	dispatch_date date
);

insert into orders values
(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');

这题目有 2 个问题容易忽略：

1. orders 表中没有销量的书
2. orders 表中在指定时间内没有销量的书
   1. books 左连 orders ，容易将 dispatch_date 的判断放在 where 中，以至于筛选不出指定时间内没有销量的书

这题的方法有很多，都是 books 连接 orders ，却别是在 ①、②、③、④ 写法不一样

select book_id, name from ① left join ② on ③ where ④;

SQL：方法一

select books.book_id, books.name from books left join (
	select book_id, sum(quantity) as total_sales
	from orders where datediff('2019-06-23', dispatch_date) < 365
	group by book_id
) as temp on books.book_id = temp.book_id
where datediff('2019-06-23', available_from) > 30
and (total < 10 or isnull(total_sales));

select temp1.book_id, temp1.name from (
	select book_id, name from books
	where datediff('2019-06-23', available_from) > 30
) as temp1 left join (
	select book_id, sum(quantity) as total_sales from orders
	where datediff('2019-06-23', dispatch_date) < 365
	group by book_id
) as temp2 on temp1.book_id = temp2.book_id
where ifnull(total_sales, 0) < 10;

解析

• 方法一是将 orders 进行分组
• 对 dispatch_date 的筛选就可以放在 orders 中了
• 对于上面说的两个问题，可以在连接后的表中统一处理
  • ifnull()
  • isnull()
  • is null

SQL：方法二

select books.book_id, books.name from books left join orders
on books.book_id = orders.book_id
and ifnull(datediff('2019-06-23', dispatch_date), 0) < 365
where datediff('2019-06-23', available_from) > 30
group by books.book_id, name having ifnull(sum(quantity), 0) < 10;

select temp1.book_id, temp1.name from (
	select book_id, name from books
	where datediff('2019-06-23', available_from) > 30
) as temp1 left join orders on temp1.book_id = orders.book_id
and ifnull(datediff('2019-06-23', dispatch_date), 0) < 365
group by temp1.book_id, temp1.name having ifnull(sum(quantity), 0) < 10;

解析

• 方法二是将两个表先连接，在进行分组处理，并用 having 筛选
• 这里要注意的一点是判断 dispatch_date 要放在 on 当中，不能放在后面的 where 中