将行与另一个表中的MAX行联接在一起? [英] Join row with MAX row in another table?

问题描述

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

How do I join a row from one table to a row with the MAX value for a given column on another table?

例如，我有一个auctions表和一个auction_bids表.我想将出价表中出价最高的拍卖表(即列bid_amount和其中auction_id = x的最高值)加入拍卖表.

For example, I have a auctions table and an auction_bids table. I want to join the auctions table with the highest bid for that auction (i.e. highest value for column bid_amount AND where auction_id = x) in the auction_bids table.

推荐答案

这很烦人.您最好在每个获胜的Auction_bid中都带有获胜者"标志.

It's annoyingly complicated. You'd be better off with a "winner" flag in each winning 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

请注意，出价为零的拍卖将根本不会列出，而有关联的拍卖(会发生吗?)对于每个并列的出价都会出现一次.

Note that auctions with zero bids will not be listed at all, and auctions with ties (can that happen?) will appear once for each tied bid.

这篇关于将行与另一个表中的MAX行联接在一起?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！