MySQL基于多列筛选唯一值：获取同品牌型号最高出价

要获取每个brand和model组合下bid_price最高的记录，可以通过以下两种方式修改查询语句：

方案一：使用窗口函数（推荐，适用于支持窗口函数的数据库如MySQL 8+、PostgreSQL等） #

利用ROW_NUMBER()窗口函数按brand和model分组，再按bid_price降序排序，筛选出每组的第一条记录（即最高出价）：

SELECT *
FROM (
    SELECT 
        brand, 
        model, 
        buyer_rate, 
        DATE_ADD(finish_dt, INTERVAL 33 HOUR) AS adjusted_finish_dt,
        bid_price, 
        g.status, 
        ga.idx AS gai, 
        ab.idx as abi,
        ROW_NUMBER() OVER (PARTITION BY brand, model ORDER BY bid_price DESC) AS rn
    FROM auction_bid ab 
    INNER JOIN goods_auction ga ON ab.goods_auction_idx=ga.idx 
    INNER JOIN auction_info ai ON ga.auction_info_idx=ai.idx 
    INNER JOIN goods g ON ai.goods_idx= g.idx 
    INNER JOIN user u on g.user_idx=u.idx 
    WHERE g.status='A008' AND DATEDIFF(Now(), ab.created_at)<5
) t
WHERE rn = 1;

• PARTITION BY brand, model：按品牌和型号分组
• ORDER BY bid_price DESC：每组内按出价从高到低排序
• rn = 1：保留每组的第一条（最高出价）记录

方案二：子查询关联（适用于不支持窗口函数的旧版本数据库） #

先通过子查询获取每个brand和model的最高bid_price，再关联原表筛选对应记录：

SELECT 
    ab_out.brand, 
    ab_out.model, 
    ab_out.buyer_rate, 
    DATE_ADD(ab_out.finish_dt, INTERVAL 33 HOUR) AS adjusted_finish_dt,
    ab_out.bid_price, 
    ab_out.status, 
    ab_out.gai, 
    ab_out.abi
FROM (
    SELECT 
        brand, 
        model, 
        buyer_rate, 
        finish_dt,
        bid_price, 
        g.status, 
        ga.idx AS gai, 
        ab.idx as abi
    FROM auction_bid ab 
    INNER JOIN goods_auction ga ON ab.goods_auction_idx=ga.idx 
    INNER JOIN auction_info ai ON ga.auction_info_idx=ai.idx 
    INNER JOIN goods g ON ai.goods_idx= g.idx 
    INNER JOIN user u on g.user_idx=u.idx 
    WHERE g.status='A008' AND DATEDIFF(Now(), ab.created_at)<5
) ab_out
INNER JOIN (
    SELECT brand, model, MAX(bid_price) AS max_bid
    FROM auction_bid ab 
    INNER JOIN goods_auction ga ON ab.goods_auction_idx=ga.idx 
    INNER JOIN auction_info ai ON ga.auction_info_idx=ai.idx 
    INNER JOIN goods g ON ai.goods_idx= g.idx 
    INNER JOIN user u on g.user_idx=u.idx 
    WHERE g.status='A008' AND DATEDIFF(Now(), ab.created_at)<5
    GROUP BY brand, model
) ab_max ON ab_out.brand = ab_max.brand AND ab_out.model = ab_max.model AND ab_out.bid_price = ab_max.max_bid;

• 子查询ab_max：计算每个品牌+型号的最高出价
• 关联原查询结果ab_out：筛选出出价等于对应组最高出价的记录

注：原查询中的GROUP BY bid_price DESC, ab.created_at不符合标准SQL语法（SELECT中多数列不在GROUP BY中），在严格模式下会报错，因此上述方案替换了原有的分组逻辑。

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