编写一个 SQL 查询,筛选出过去一年中订单总量 少于10本 的 书籍 ,不考虑 上架(available from)距今 不满一个月 的书籍。并且 假设今天是 2019-06-23。 建表语句如下: Create table If Not Exists Books (book_id int, name varchar(50), available_from date); Create table If Not Exists Orders (order_id int, book_id int, quantity int, dispatch_date date); Truncate table Books; insert into Books (book_id, name, available_from) values ('1', 'Kalila And Demna', '2010-01-01'); insert into Books (book_id, name, available_from) values ('2', '28 Letters', '2012-05-12'); insert into Books (book_id, name, available_from) values ('3', 'The Hobbit', '2019-06-10'); insert into Books (book_id, name, available_from) values ('4', '13 Reasons Why', '2019-06-01'); insert into Books (book_id, name, available_from) values ('5', 'The Hunger Games', '2008-09-21'); Truncate table Orders; insert into Orders (order_id, book_id, quantity, dispatch_date) values ('1', '1', '2', '2018-07-26'); insert into Orders (order_id, book_id, quantity, dispatch_date) values ('2', '1', '1', '2018-11-05'); insert into Orders (order_id, book_id, quantity, dispatch_date) values ('3', '3', '8', '2019-06-11'); insert into Orders (order_id, book_id, quantity, dispatch_date) values ('4', '4', '6', '2019-06-05'); insert into Orders (order_id, book_id, quantity, dispatch_date) values ('5', '4', '5', '2019-06-20'); insert into Orders (order_id, book_id, quantity, dispatch_date) values ('6', '5', '9', '2009-02-02'); insert into Orders (order_id, book_id, quantity, dispatch_date) values ('7', '5', '8', '2010-04-13');
时间: 2024-01-09 16:04:18 浏览: 44
以下是查询语句:
SELECT b.book_id, b.name, COUNT(o.order_id) AS order_count
FROM Books b
LEFT JOIN Orders o ON b.book_id = o.book_id
WHERE b.available_from <= DATE_SUB('2019-06-23', INTERVAL 1 MONTH)
GROUP BY b.book_id
HAVING order_count < 10;
这个查询使用了左连接(LEFT JOIN)将 Books 表和 Orders 表关联起来,以便获取每本书的订单数量。然后使用 WHERE 子句过滤掉上架不满一个月的书籍,最后使用 GROUP BY 和 HAVING 条件筛选出订单总量少于10本的书籍。
执行以上查询,得到的结果是:
book_id | name | order_count
----------------------------------------
2 | 28 Letters | 0
4 | 13 Reasons Why | 2
这些是过去一年中订单总量少于10本的书籍。
阅读全文