如何用SQL筛选同collectionId下唯一cardNum的最高价格记录并降序排序？

原始表数据 #

需求说明 #

筛选出同一collectionId下每个cardNum对应的最高价格记录，最终结果按price降序排列。（注：你给出的期望结果中id9的price为0.32，应为笔误，原表对应值为0.30）

已尝试的无效SQL #

SELECT id FROM table_name GROUP BY cardNum,collectionId ORDER BY price DESC

SELECT 
  t1.id
FROM 
  table_name AS t1
LEFT JOIN 
  table_name AS t2
ON 
  t1.cardNum < t2.cardNum
AND 
  t1.collectionId = t2.collectionId
GROUP BY 
  t1.cardNum,t1.collectionId
ORDER BY 
  t1.price DESC

方案1：窗口函数（推荐，支持MySQL8.0+/PostgreSQL/SQL Server等） #

利用ROW_NUMBER()窗口函数分组排序，精准获取每组最高价格的记录：

SELECT id, price
FROM (
    SELECT 
        id, 
        price,
        ROW_NUMBER() OVER (
            PARTITION BY collectionId, cardNum 
            ORDER BY price DESC
        ) AS rn
    FROM table_name
) AS sub_query
WHERE rn = 1
ORDER BY price DESC;

逻辑说明 #

• PARTITION BY collectionId, cardNum：按collectionId和cardNum组合分组
• ORDER BY price DESC：每组内按价格降序排列，最高价格的记录会被标记为rn=1
• 外层筛选rn=1的记录，最后按价格降序输出

方案2：JOIN+聚合函数（兼容旧版MySQL） #

如果数据库不支持窗口函数，可通过子查询聚合最高价格，再关联原表获取对应记录：

SELECT t1.id, t1.price
FROM table_name t1
INNER JOIN (
    SELECT 
        collectionId, 
        cardNum, 
        MAX(price) AS max_price
    FROM table_name
    GROUP BY collectionId, cardNum
) AS t2 
ON t1.collectionId = t2.collectionId 
AND t1.cardNum = t2.cardNum 
AND t1.price = t2.max_price
ORDER BY t1.price DESC;

逻辑说明 #

• 子查询先计算每个(collectionId, cardNum)分组的最高价格
• 将原表与子查询结果关联，匹配分组和价格，得到对应记录
• 最后按价格降序排序

之前SQL无效的原因 #

• 第一种GROUP BY写法：分组后直接取id，数据库会随机返回分组内的一条id，无法保证是最高价格对应的记录，排序逻辑也不成立
• 第二种LEFT JOIN写法：ON条件错误，应该匹配同分组内价格更高的记录，而非cardNum更大的记录，逻辑方向错误

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