SQL中关联外键表结合GROUP BY查询每类最贵书籍的错误修正

原有SQL存在的问题 #

• 没有指定Book表和Details表的关联条件，使用的隐式交叉连接会生成两表数据的笛卡尔积，产生大量无效的中间数据，最终计算出的最高价格完全不符合实际业务逻辑
• 如果需求是获取每个分类下最贵的书籍完整信息，原有写法仅能拿到分类对应的最高价格，无法匹配到对应的书籍属性

修正方案 #

方案1：仅需获取每个分类的最高价格 #

如果只需要每个分类对应的最高价格数值，加上两表的关联条件即可，推荐使用显式INNER JOIN写法，可读性和可维护性更强：

SELECT 
    b.Genre,
    MAX(d.Price) AS MAX_Price
FROM Book b
INNER JOIN Details d 
    ON b.ISBN = d.ISBN
GROUP BY b.Genre;

方案2：需要获取每个分类下最贵的书籍完整信息 #

如果需要拿到最高价格对应的书籍名称、ISBN、页数等完整信息，可以使用窗口函数实现：

WITH ranked_books AS (
    SELECT 
        b.ISBN,
        b.Name,
        b.Genre,
        d.Price,
        d.Total_pages,
        -- 按分类分组，价格从高到低排序
        RANK() OVER (PARTITION BY b.Genre ORDER BY d.Price DESC) AS price_rank
    FROM Book b
    INNER JOIN Details d 
        ON b.ISBN = d.ISBN
)
-- 取每个分类下排名第一的书籍，支持并列最高的场景
SELECT ISBN, Name, Genre, Price, Total_pages
FROM ranked_books
WHERE price_rank = 1;

如果不需要返回并列最高的所有书籍，仅需任意一本最高价格的书籍，可以把RANK()替换为ROW_NUMBER()。

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