如何在SQLite中编写满足图书销量对比条件的子查询？

实现代码 #

WITH book_avg_sales AS (
    SELECT 
        b.book_id,
        b.book_description,
        b.book_type_code,
        AVG(s.amount_of_books_sold) AS 单本图书平均销量
    FROM books b
    LEFT JOIN sold_books s ON b.book_id = s.book_id
    WHERE s.sold_books_status != 'canceled' OR s.sold_books_status IS NULL
    GROUP BY b.book_id, b.book_description, b.book_type_code
),
type_avg_sales AS (
    SELECT 
        book_type_code,
        AVG(单本图书平均销量) AS 类别平均销量
    FROM book_avg_sales
    GROUP BY book_type_code
)
SELECT 
    bt.book_type_description,
    bt.book_type_code,
    bas.book_description,
    bas.单本图书平均销量,
    tas.类别平均销量
FROM book_avg_sales bas
JOIN type_avg_sales tas ON bas.book_type_code = tas.book_type_code
JOIN book_type bt ON bas.book_type_code = bt.book_type_code
WHERE bas.单本图书平均销量 < tas.类别平均销量
ORDER BY bt.book_type_code, bas.单本图书平均销量;

代码说明 #

• book_avg_sales CTE：关联图书表和销售记录表，过滤掉状态为canceled的销售记录（同时兼容无销售记录的图书），按单本图书分组计算每本书的平均销量。
• type_avg_sales CTE：基于单本图书的平均销量数据，按图书类别分组，计算每个类别的整体平均销量。
• 最终查询：关联两个统计结果和图书类别表，筛选出单本平均销量低于所属类别平均的图书，返回要求的所有字段。

注意点 #

• 用LEFT JOIN关联sold_books是为了保留无销售记录的图书（这类图书的平均销量为NULL，会自动被最后的<条件过滤）。
• 条件s.sold_books_status != 'canceled' OR s.sold_books_status IS NULL确保只统计有效销售记录，同时不排除无销售的图书。

内容的提问来源于stack exchange，提问作者Erika