LeetCode 1098.小众书籍

最新推荐文章于 2024-09-13 18:11:35 发布

奋斗哼哼

最新推荐文章于 2024-09-13 18:11:35 发布

阅读量134

点赞数

文章标签： leetcode 数据库 mysql sql

本文链接：https://blog.csdn.net/weixin_51696882/article/details/131253550

版权

该段SQL代码用于从Books和Orders表中查询在过去一年内订单总量少于10本的书籍信息。首先创建了两个临时表t1和t2，分别处理当前时间前一月的日期和找出发布时间在一个月以内的书籍。然后通过关联查询找出订单数量不足10的书籍ID和名称。

摘要由CSDN通过智能技术生成

数据准备

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');

输入

在这里插入图片描述

需求

写一段 SQL 命令，筛选出过去一年中订单总量少于10本的书籍

输出

# str_to_date 字符串转日期函数
# date_format 日期转字符串函数
# 在t1中求出当前时间前一月的日期
# 在t2中求出发布时间距今不满一月的数据
# 最后和Orders表进行关联,求出订单总量小于10的书籍
with t1 as (
    select *,
       date_add(str_to_date('2019-06-23','%Y-%m-%d'),interval -30 day ) as dt1
    from Books
),t2 as (
    select *
    from t1
    where available_from<=dt1
)
select t2.book_id,name
from t2,Orders
where t2.book_id=order_id and quantity<10
;