SQL查询：如何获取对应最高出价金额的出价日期？

要获取每个listing对应最高出价的日期，不能直接把bids.date加入GROUP BY（这会把每个出价日期单独分组，导致同一listing出现多行），可以用以下两种常用方案：

方案一：窗口函数（推荐，兼容多数现代数据库） #

通过ROW_NUMBER()窗口函数给每个listing的出价按金额降序排序，标记出最高出价的那条记录，再结合聚合统计总出价数：

WITH ranked_bids AS (
    SELECT
        listing_id,
        amount,
        date,
        ROW_NUMBER() OVER (PARTITION BY listing_id ORDER BY amount DESC) AS rn
    FROM bids
)
SELECT
    l.title,
    COUNT(b.id) AS number_of_bids,
    MAX(b.amount) AS highest_bid_amount,
    rb.date AS highest_bid_date
FROM listings l
LEFT JOIN bids b ON l.id = b.listing_id
LEFT JOIN ranked_bids rb ON l.id = rb.listing_id AND rb.rn = 1
GROUP BY l.title, rb.date;

如果同一listing存在多个相同最高金额的出价，ROW_NUMBER()会随机选一条；若要返回所有最高出价的日期，可改用RANK()或DENSE_RANK()，并调整后续逻辑。

方案二：关联子查询 #

先查询每个listing的最高出价金额，再关联回bids表匹配对应日期，同时统计总出价数：

SELECT
    l.title,
    COUNT(b.id) AS number_of_bids,
    MAX(b.amount) AS highest_bid_amount,
    (SELECT date FROM bids WHERE listing_id = l.id AND amount = MAX(b.amount)) AS highest_bid_date
FROM listings l
LEFT JOIN bids b ON l.id = b.listing_id
GROUP BY l.title;

注意：如果同一listing有多个相同最高金额的出价，这个子查询会报错（返回多行），此时可以用MAX(date)或MIN(date)来取其中一个日期，修改为：

SELECT
    l.title,
    COUNT(b.id) AS number_of_bids,
    MAX(b.amount) AS highest_bid_amount,
    (SELECT MAX(date) FROM bids WHERE listing_id = l.id AND amount = MAX(b.amount)) AS highest_bid_date
FROM listings l
LEFT JOIN bids b ON l.id = b.listing_id
GROUP BY l.title;

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