在JOIN中获得最高的结果 [英] Getting highest results in a JOIN

问题描述

我有三张桌子；拍卖，竞标和用户.表结构看起来像这样:

I've got three tables; Auctions, Auction Bids and Users. The table structure looks something like this:

Auctions:

 id    title
 --    -----
  1     Auction 1
  2     Auction 2

Auction Bids:

 id    user_id    auction_id    bid_amt
 --    -------    ----------    -------
  1       1            1          200.00
  2       2            1          202.00
  3       1            2          100.00

Users只是一个标准表，具有ID和用户名.

Users is just a standard table, with id and user name.

我的目标是加入这些表格，以便获得这些出价的最高价值以及与这些出价相关的用户名；所以我有一个这样的结果集:

My aim is to join these tables so I can get the highest values of these bids, as well as get the usernames related to those bids; so I have a result set like so:

auction_id    auction_title    auctionbid_amt    user_username
----------    -------------    --------------    -------------
         1    Auction 1            202.00            Bidder2
         2    Auction 2            100.00            Bidder1

到目前为止，我的查询如下:

So far my query is as follows:

SELECT a.id, a.title, ab.bid_amt, u.display_name FROM auction a
    LEFT JOIN auctionbid ab ON a.id = ab.auction_id
        LEFT JOIN users u ON u.id = ab.user_id
GROUP BY a.id

这会得到我所关注的单行，但似乎显示的是最低的bid_amt，而不是最高的.

This gets the single rows I am after, but it seems to display the lowest bid_amt, not the highest.

推荐答案

您可以使用MAX函数和子选项来获取每次拍卖的最高出价.如果将此子选择项与其他表连接起来，并按如下所示设置where子句，则应该得到所需的内容.

You can use the MAX-Function and a sub-select to get the maximum bid for each auction. If you join this subselect with your other tables and set the where clause as follows you should get what you are looking for.

SELECT a.id, a.title, ab.bid_points, u.display_name 
FROM Auction AS a
INNER JOIN (SELECT auction_id, MAX(bid_points) AS maxAmount FROM auction_bids GROUP BY auction_id) AS maxBids ON maxBids.auction_id = a.id
INNER JOIN auction_bids AS ab ON a.id = ab.auction_id
INNER JOIN users AS u ON u.id = ab.user_id
WHERE ab.auction_id = maxBids.auction_id AND ab.bid_amount = maxBids.maxAmount

希望有帮助.

这篇关于在JOIN中获得最高的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！