使用临时表计算SUM与AVG的SQL查询问题及优化咨询

需求回顾 #

找出所有副本总价值低于所有图书副本平均总价值的图书标题，规则：若图书未指定价格，默认按15计算。

初始代码问题分析 #

初始代码返回49条重复数据，核心原因是笛卡尔积爆炸：

• CopiesSum 中GROUP BY copyISBN是错误的（应为copy.ISBN或book.ISBN），且未关联图书标题，导致后续与book表关联时无匹配条件；
• 最终查询用逗号连接book, CopiesSum, AveragePrice，未设置任何关联条件，三者直接产生笛卡尔积：7本图书 × 7条CopiesSum数据 × 1条AveragePrice数据 = 49条重复结果。

初始代码（标记错误点）：

WITH CopiesSum AS (
        SELECT  SUM(COALESCE(book.cost, 15)) AS total_sum
        FROM    copy, book
        WHERE   book.ISBN = copy.ISBN
        GROUP BY copyISBN -- 错误：字段名应为copy.ISBN，且未包含book.title
),
AveragePrice AS (
        SELECT  AVG(COALESCE(book.cost, 15)) AS average_price
        FROM    book, copy
        WHERE   book.ISBN = copy.ISBN
)
SELECT book.title, total_sum, average_price
FROM    book, CopiesSum, AveragePrice -- 错误：无关联条件，产生笛卡尔积
GROUP BY book.title, AveragePrice.average_price, CopiesSum.total_sum;

修改后代码的问题与规范优化 #

修改后的代码修复了部分问题，但仍有两处明显错误，且ON 1=1的连接方式不够规范：

1. CTE名称不匹配：VidutinesVertes定义后，查询中却用AveragePrice；
2. 表名拼写错误：knyga.isbn应为book.isbn；
3. ON 1=1本质是CROSS JOIN（笛卡尔积），由于AveragePrice只有一行全局平均值，结果行数是对的，但写法不直观，用CROSS JOIN更符合SQL规范。

修正后的修改版代码：

WITH CopiesSum AS (
        SELECT  book.title,
                SUM(COALESCE(book.cost, 15)) AS total_sum
        FROM    copy
        JOIN    book ON book.ISBN = copy.ISBN
        GROUP BY book.title -- 建议同时按book.ISBN分组，避免同书名不同ISBN的图书被合并
),
AveragePrice AS ( -- 修正CTE名称
        SELECT  AVG(COALESCE(book.cost, 15)) AS average_price
        FROM    book
        JOIN    copy ON book.isbn = copy.ISBN -- 修正表名拼写
)
SELECT  title, total_sum, average_price
FROM    CopiesSum
CROSS JOIN AveragePrice; -- 用CROSS JOIN替代ON 1=1，更清晰规范

更简洁高效的最终写法 #

考虑到需求是筛选总价值低于平均值的图书，推荐直接将平均值作为标量子查询嵌入WHERE条件，无需额外JOIN：

WITH BookCopyTotal AS (
    -- 计算每本图书的副本总价值，按ISBN+标题分组确保准确性
    SELECT 
        book.title,
        SUM(COALESCE(book.cost, 15)) AS total_value
    FROM copy
    JOIN book ON book.ISBN = copy.ISBN
    GROUP BY book.ISBN, book.title
)
SELECT title, total_value
FROM BookCopyTotal
WHERE total_value < (SELECT AVG(total_value) FROM BookCopyTotal);

这种写法逻辑清晰，避免了不必要的JOIN，性能更优。

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