查找特定投标人的所有获胜拍卖

Question

我们有两个表用于存储拍卖& &;它的出价信息。

auction(存储拍卖信息)

auction_id auction_name
---------- ---------------------
2216 Test Auction
...

auction_bid(存储拍卖出价)

bid_id amount bidder auction_id
------ -------- ------ ------------
8398 50.0000 53 2216
8397 50.0000 32 2216
8396 20.0000 53 2216
...

注:以出价最高者为中标者。如有相同的出价，最后一个出价最高的人被宣布为获胜者。例如，在上述情况下，中标人是53

现在我想在用户仪表板上显示所有获胜的拍卖信息。例如，投标人= 53我使用了以下查询:

SELECT DISTINCT 
  a.*,
  b.bid_id,
  b.amount,
  b.bidder 
FROM
  auction a 
  LEFT JOIN auction_bid b 
    ON a.auction_id = b.auction_id 
WHERE b.bidder = 53 
  AND b.amount = 
  (SELECT 
    MAX(amount) 
  FROM
    auction_bid tmp 
  WHERE tmp.auction_id = b.auction_id 
  LIMIT 0, 1)

这适用于投标人= 53。但对于投标人= 32，同样的拍卖也显示为获胜拍卖。

我知道这个问题是由投标引起的。如何修改上述查询，使其适用于竞投?非常感谢您的帮助。

对

Answer 1

我会使用bid_id作为您的过滤器，既为了效率，又确保每次拍卖只有一条记录:

投标人53:

SELECT
  a.*,
  b.bid_id,
  b.amount,
  b.bidder 
FROM
  auction a 
  LEFT JOIN auction_bid b 
    ON a.auction_id = b.auction_id 
WHERE b.bidder = 53 
  AND b.bid_id = 
  (SELECT bid_id
   FROM auction_bid 
   WHERE auction_id = b.auction_id 
   ORDER BY amount desc,bid_id desc
   LIMIT 0,1);

结果:

AUCTION_ID    AUCTION_NAME    BID_ID    AMOUNT    BIDDER
----------    ------------    ------    ------    ------
2216          Test Auction    8398      50         53

投标人32:

SELECT
  a.*,
  b.bid_id,
  b.amount,
  b.bidder 
FROM
  auction a 
  LEFT JOIN auction_bid b 
    ON a.auction_id = b.auction_id 
WHERE b.bidder = 32 
  AND b.bid_id = 
  (SELECT bid_id
   FROM auction_bid 
   WHERE auction_id = b.auction_id 
   ORDER BY amount desc,bid_id desc
   LIMIT 0,1)

0行返回:http://www.sqlfiddle.com/#!2/beb2e3/6

Answer 2

您可以尝试将以下代码添加到查询的末尾:

AND NOT EXISTS (select * from auction_bid tmp2 where tmp2.auction_id=b.auction_id and tmp2.amount = b.amount AND tmp2.bid_id>b.bid_id)

这将检查以确保在给定出价之后的同一拍卖中没有相同金额的出价(假设您告诉后一个出价的方式是更高的bid_id)。

Answer 3

试试这个:

SELECT DISTINCT 
  a.*,
  b.bid_id,
  b.amount,
  b.bidder 
FROM
  auction a 
  LEFT JOIN auction_bid b 
    ON a.auction_id = b.auction_id 
WHERE b.bidder = 53 
  AND b.amount = 
  (SELECT 
    MAX(amount) 
  FROM
    auction_bid tmp 
  WHERE tmp.auction_id = b.auction_id 
  LIMIT 0, 1)
GROUP BY b.bid_id
HAVING MAX(b.bid_id)

你也可以试试这个，但我不能确定这将工作没有测试:

SELECT
  a.*,
  b.bid_id,
  b.amount,
  b.bidder 
FROM
  auction AS a 
  LEFT JOIN auction_bid AS b 
    ON a.auction_id = b.auction_id 
WHERE b.bidder = 53 
GROUP BY b.bid_id
HAVING MAX(b.amout) AND MAX(b.bid_id);