PieCloudDB数据库中筛选低销量图书的SQL查询问题排查

安全阿岳

2026-7-3

问题排查：筛选符合条件的图书SQL修正

表结构与数据

Books表（图书信息）

book_id	name	launch_date
1	Pride and Prejudice	2010-01-01
2	The Kite Runner	2012-05-12
3	The Da Vinci Code	2023-11-10
4	Wuthering Heights	2022-12-01
5	The Moon and Sixpence	2018-09-21
6	Three Days To See	2023-12-20

Orders表（订单信息）

order_id	book_id	quantity	order_date
1	1	2	2020-07-26
2	1	1	2023-08-05
3	3	8	2023-11-19
4	4	6	2023-12-05
5	4	5	2023-12-10
6	5	9	2019-02-02
7	5	8	2020-04-13

需求说明

需筛选满足以下条件的图书：

过去一年（2022-12-20 至 2023-12-20）总订单量少于10
排除发布日期不足一个月的图书（今日为2023-12-20，即launch_date < '2023-11-20'）

预期结果

book_id	name
1	Pride and Prejudice
2	The Kite Runner
3	The Da Vinci Code
5	The Moon and Sixpence

用户原SQL及错误结果

原SQL代码

select book_id,name
from books
where launch_date < '2023-11-20'
and
book_id in (
select book_id
from orders
where order_date between '2022-12-20' and '2023-12-20'
group by book_id
having sum(quantity) < 10);

错误查询结果

book_id	name
1	Pride and Prejudice
3	The Da Vinci Code

问题分析

遗漏无订单的图书：原SQL的IN子查询仅包含过去一年有订单记录的图书，但像book_id=2、book_id=5这类过去一年没有订单的图书，总订单量为0（满足<10的条件），却被排除在外。
逻辑覆盖不全：需求中“总订单量少于10”包含订单量为0的情况，原SQL未处理这类场景。

修正后的SQL

SELECT b.book_id, b.name
FROM books b
LEFT JOIN (
    SELECT book_id, SUM(quantity) AS total_quantity
    FROM orders
    WHERE order_date BETWEEN '2022-12-20' AND '2023-12-20'
    GROUP BY book_id
) o ON b.book_id = o.book_id
WHERE b.launch_date < '2023-11-20'
AND COALESCE(o.total_quantity, 0) < 10;