获取各维度最高定价图书的MySQL查询问题求助

你的现有查询仅能得到每个subject, author, class, publisher, region分组下的最高定价，但如果需要获取该最高定价对应的完整图书记录（比如id字段），这个查询无法满足需求——因为当分组内存在多条记录时，非聚合列（如id）会返回随机值（若关闭ONLY_FULL_GROUP_BY），或直接触发语法错误（若开启该模式）。

以下是两种正确的实现方式：

方法一：使用窗口函数（MySQL 8.0及以上版本） #

利用ROW_NUMBER()窗口函数，按指定维度分组后，对每组内的图书按price降序排序，取排序后行号为1的记录，就是该分组下定价最高的图书：

SELECT id, price, subject, author, class, publisher, region
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY subject, author, class, publisher, region
            ORDER BY price DESC
        ) AS rn
    FROM your_table_name
) AS t
WHERE rn = 1;

如果分组内存在多本定价相同的最高价格图书，且需要全部返回，可将ROW_NUMBER()替换为RANK()。

方法二：使用关联子查询（兼容MySQL 5.x版本） #

先通过子查询获取每个分组的最高定价，再关联原表筛选出对应分组中价格等于该最高定价的记录：

SELECT t1.*
FROM your_table_name t1
JOIN (
    SELECT 
        subject, 
        author, 
        class, 
        publisher, 
        region, 
        MAX(price) AS highest_price
    FROM your_table_name
    GROUP BY subject, author, class, publisher, region
) AS t2 
ON t1.subject = t2.subject
AND t1.author = t2.author
AND t1.class = t2.class
AND t1.publisher = t2.publisher
AND t1.region = t2.region
AND t1.price = t2.highest_price;

这个方法会返回分组内所有定价等于最高价格的图书，若只需要单条记录，可结合DISTINCT或LIMIT调整。

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