mysql - 在另一个表中加入具有 MAX 行的行？

Question

如何将一个表中的行连接到另一个表中给定列的具有 MAX 值的行？

例如，我有一个auctions 表和一个auction_bids 表。我想加入该拍卖的最高出价的拍卖表(即列 bid_amount AND where auction_id = x 的最高值)在auction_bids 表。

Answer 1

这是烦人的复杂。在每次获胜的 auction_bid 中，最好有一个“获胜者”标志。

SELECT * FROM auctions a
INNER JOIN 
(
    /* now get just the winning rows */
    SELECT * FROM auction_bids x
    INNER JOIN
    (
        /* how to tell the winners */
        SELECT auction_id, MAX(bid_amount) as winner
        FROM auction_bids
        GROUP BY auction_id
    ) y
    ON x.auction_id = y.auction_id
    AND x.bid_amount = y.winner
) b
ON a.auction_id = b.auction_id

请注意，零出价的拍卖根本不会列出，平价的拍卖(这会发生吗？)将针对每个平价出现一次。