MySQL查询拍卖中指定供应商最新出价的排名问题求助

原查询的核心错误 #

• 生成sorted_vendors时，直接使用原始bids表的bid_price排序，未基于每个供应商的最新出价计算，排序依据完全偏离需求。
• GROUP_CONCAT(DISTINCT vendor_id ORDER BY bid_price)中的排序逻辑，用的是供应商任意一条出价的价格，而非最新有效出价，导致排名计算彻底错误。

修正后的查询方案 #

方案1：使用窗口函数（MySQL 8.0+ 推荐） #

该方式逻辑清晰，支持处理相同出价的排名场景（RANK()会给相同出价的供应商相同排名，后续排名跳过；若需连续排名可改用DENSE_RANK()）：

SET @vendor_id := 5;
SET @auction_id := 7;

SELECT
    vendor_id,
    latest_bid_price AS lowest_bid,
    rank AS position
FROM (
    SELECT
        b1.vendor_id,
        b1.bid_price AS latest_bid_price,
        RANK() OVER (ORDER BY bid_price ASC) AS rank
    FROM bids b1
    INNER JOIN (
        -- 先获取每个供应商在指定拍卖下的最新出价时间
        SELECT vendor_id, MAX(created_at) AS latest_created_at
        FROM bids
        WHERE auction_id = @auction_id
        GROUP BY vendor_id
    ) latest_bids 
        ON b1.vendor_id = latest_bids.vendor_id 
        AND b1.created_at = latest_bids.latest_created_at
    WHERE b1.auction_id = @auction_id
) AS ranked_bids
WHERE vendor_id = @vendor_id;

方案2：使用变量兼容MySQL 5.x版本 #

若需兼容低版本MySQL，可通过变量模拟排名逻辑：

SET @vendor_id := 5;
SET @auction_id := 7;
SET @rank := 0;
SET @prev_price := NULL;

SELECT
    vendor_id,
    latest_bid_price AS lowest_bid,
    position
FROM (
    SELECT
        vendor_id,
        latest_bid_price,
        -- 处理相同出价的排名逻辑，与RANK()行为一致
        CASE 
            WHEN @prev_price = latest_bid_price THEN @rank
            ELSE @rank := @rank + 1
        END AS position,
        @prev_price := latest_bid_price
    FROM (
        -- 获取所有供应商的最新出价并按价格升序排序
        SELECT
            b1.vendor_id,
            b1.bid_price AS latest_bid_price
        FROM bids b1
        INNER JOIN (
            SELECT vendor_id, MAX(created_at) AS latest_created_at
            FROM bids
            WHERE auction_id = @auction_id
            GROUP BY vendor_id
        ) latest_bids 
            ON b1.vendor_id = latest_bids.vendor_id 
            AND b1.created_at = latest_bids.latest_created_at
        WHERE b1.auction_id = @auction_id
        ORDER BY latest_bid_price ASC
    ) AS sorted_latest_bids
) AS ranked_bids
WHERE vendor_id = @vendor_id;

内容的提问来源于stack exchange，提问作者Muntashir Are Rahi