SQL案例-左连接/时间函数的使用_#编写一个 sql查询,筛选出过去一年中订单总量少于10本的书籍 ,不考虑上架(avai-CSDN博客

该文提供了一个SQL查询示例，目的是找出过去一年订单总量少于10本，并且上架超过一个月的书籍。首先创建并填充了Books和Orders两个表格，然后逐步构建查询语句，包括左连接以包含未销售的书籍，使用datediff函数判断日期条件，以及通过子查询和聚合函数计算销售总量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- 需求：筛选出过去一年中订单总量 少于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');

-- 需求：筛选出过去一年中订单总量 少于10本 的 书籍 ，不考虑 上架（available from）距今 不满一个月 的书籍。并且 假设今天是 2019-06-23

学思路：


-- 1.需求最终输出的结果是：name =》books表 =》左连接：如果销售书籍为0的时候也需显示书籍名称
select b.name,o.quantity
from Books b left join Orders o on b.book_id=o.book_id;

-- 2.对查询出的结果进行判断 ：销售时间在一年内的=>现在-过去>>0，上架时间大于1个月=>现在-过去>>0
-- 测试函数=》上面的结果是不是恒大于或等于0
-- select available_from,datediff('2019-06-23',available_from) as a from Books;

select b.name,o.quantity
from Books b left join Orders o on b.book_id=o.book_id
where datediff('2019-06-23',o.dispatch_date)<=365
and datediff('2019-06-23',b.available_from)>30;

-- 3.对时间判断的基础上判断，销售总数<10本的书
-- =>想要将一个查询语句当作表使用，需要查询出来的结果是一个数据集
-- 3-1-1) 对销售的书籍数量求和-初步
select sum(quantity) as num,b.name  from Books b left join Orders o on b.book_id=o.book_id  group by b.name ;
-- 3-1-2) 对销售的书籍数量求和 的基础上对和判断-初步
select t1.name,t1.num from
              (select sum(quantity) as num,b.name  from Books b left join Orders o on b.book_id=o.book_id group by b.name ) as t1
where t1.num < 10;

-- 3-1）对销售的书籍数量求和，加入之前写好的：时间判断=作为一张表， 的套用
select sum(t1.quantity) as t1_num, t1.name
from
     (select b.name,o.quantity
from Books b left join Orders o on b.book_id=o.book_id
where datediff('2019-06-23',o.dispatch_date)<=365
and datediff('2019-06-23',b.available_from)>30) as t1
group by t1.name ;

-- 3-2）对销售的书籍数量求和，基础上增加：销售总数<10的判断：将售的书籍数量求和作为一张表，对表内的字段判断 的套用
select * from 
              (
select sum(t1.quantity) as t1_num, t1.name
from
     (
         select b.name,o.quantity
from Books b left join Orders o on b.book_id=o.book_id
where datediff('2019-06-23',o.dispatch_date)<=365
and datediff('2019-06-23',b.available_from)>30
     ) as t1
group by t1.name
               ) t2
where t1_num<10;