首页 文章

在另一个表中加入MAX行的行？

提问于
浏览 次
3

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

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

mysql sql

5 回答

  • 10

    这太烦人了 . 在每个获胜的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

    请注意，零投标的拍卖根本不会被列出，并且有关系的拍卖（可能会发生吗？）将针对每个并列出价出现一次 .

    回复于
  • 1

    试试这个：

    SELECT a.id, MAX(ab.bid_amount)
FROM auctions AS a
INNER JOIN action_bids AS ab 
   ON a.id = ab.auction_id
GROUP BY a.id;

    根据需要在 SELECT 和 GROUP BY 子句中添加更多列 .

    回复于
  • 0

    试试这个

    Select A.ID,max(AB.bit_amount) 
 From  auctions A Inner Join Auction_bids AB on A.ID = AB.auction_ID
 Where A.ID = x
 Group By A.ID
    回复于
  • 0

    基本上，您必须使用子查询在实际连接中执行此操作 .

    Select bid as highest_bid
  from auctions
    left outer join auctions_bids on action_bids.ref_no = auctions.ref_no
     and (select max(bid) from auctions_bids as ab 
           where auctions_bid.ref_no = a2.ref_no)
    回复于
  • 0

    试试这个：

    SELECT a.*, bid_amount
  FROM auction a INNER JOIN 
    (
     SELECT auction_id, MAX(bid_amount)  AS bid_amount
       FROM auction_bids
         WHERE acution_id = x
         GROUP BY auction_id
    ) b
    ON a.auction_id = b.auction_id
    回复于

相关问题