MySQL INNER JOIN 查找行组的最大值

Question

我已经在此处提供了我正在尝试做的示例

http://sqlfiddle.com/#!2/514d2/2

此演示查找“出价”表中不是每个 listing_id 最高出价的所有行。不过，我需要做的是找到所有列表，其中 $userid(在本例中为用户 1)是出价最高的人。

谁能帮我解决这个问题？

对于那些不使用 SQLFiddle 的人...

• 表名:listings
• 表名:bids

SQL:

SELECT listings.end_date, 
    listings.user_id, 
    listings.title, 
    listings.auc_fp, 
    listings.id, 
    listings.auc_image1 
FROM listings 
INNER JOIN 
    (SELECT 
     listing_id, 
     user_id, 
     bid maxBid 
 FROM bids 
 WHERE bid 
 NOT IN 
     (SELECT MAX(bid) 
      FROM bids) 
      GROUP BY 
         listing_id, 
         user_id) 
    bids ON 
      listings.id = bids.listing_id 
  WHERE bids.user_id=1 
  AND listings.end_date > NOW() 
  ORDER BY listings.list_ts DESC

此查询成功找到 $userid 出价但不是最高出价者的所有列表。我想找到 $userid 已经出价并且是最高出价者的所有列表。

Answer 1

初步观察

可以重新格式化原始查询以使其更易于阅读，如下所示:

SELECT listings.end_date, 
       listings.user_id, 
       listings.title, 
       listings.auc_fp, 
       listings.id, 
       listings.auc_image1 
  FROM listings 
  JOIN (SELECT listing_id, 
               user_id, 
               bid maxBid 
          FROM bids 
         WHERE bid NOT IN (SELECT MAX(bid) FROM bids) 
         GROUP BY listing_id, user_id
       ) AS bids
    ON listings.id = bids.listing_id 
 WHERE bids.user_id = 1 
   AND listings.end_date > NOW() 
 ORDER BY listings.list_ts DESC

我注意到别名为 maxBid 的列不一定是任何列表的最高出价。此查询也仅适用于仍然开放的列表。我不相信它会产生预期的答案；唯一排除的出价是包含任何列表的总体最高出价的出价。

---

TDQD — 测试驱动查询设计

真正的问题是:

Find all the currently open listings where the $userid (= 1) has placed a bid and is the highest bidder.

是时候进行 TDQD — 测试驱动的查询设计；我的大脑工作速度不够快，无法同时完成所有工作。

每个列表的最高出价

SELECT listing_id, MAX(bid) AS maxBid
  FROM bids
 GROUP BY listing_id

查找出价最高的用户是用户 1 的列表的出价

SELECT b.listing_id
  FROM bids AS b
  JOIN (SELECT listing_id, MAX(bid) AS maxBid
          FROM bids
         GROUP BY listing_id
       ) AS m
    ON m.listing_id = b.listing_id AND m.maxBid = b.bid
 WHERE b.user_id = 1

查找出价最高的用户为用户1的listing

SELECT l.*
  FROM listings AS l
  JOIN (SELECT b.listing_id
          FROM bids AS b
          JOIN (SELECT listing_id, MAX(bid) AS maxBid
                  FROM bids
                 GROUP BY listing_id
               ) AS m
            ON m.listing_id = b.listing_id AND m.maxBid = b.bid
         WHERE b.user_id = 1
       ) AS u
    ON l.id = u.listing_id

将查询限制为打开列表

SELECT l.*
  FROM listings AS l
  JOIN (SELECT b.listing_id
          FROM bids AS b
          JOIN (SELECT listing_id, MAX(bid) AS maxBid
                  FROM bids
                 GROUP BY listing_id
               ) AS m
            ON m.listing_id = b.listing_id AND m.maxBid = b.bid
         WHERE b.user_id = 1
       ) AS u
    ON l.id = u.listing_id
 WHERE l.end_date > NOW()

您可以而且应该将 * 替换为您感兴趣的确切列。如果您需要出价详细信息，您也可以获得这些信息。

---

针对 Informix 进行测试

需要进行一个小的语法更改 — NOW() 被 CURRENT YEAR TO SECOND 替换 — 但这只会影响最后一个查询。在 DDL 上需要做更多的工作才能使语法更接近 DBMS 中立的格式； MySQL 有一些奇怪的约定。

future 读者请注意:将 NOW() 或 CURRENT YEAR TO SECOND 替换为 2012-12-29 12:37:43 这样的值 得到相同的结果。数据中的列表从 `2013-01-10 00:00:001 起过期。

查询 1:

SELECT listing_id, MAX(bid) AS maxBid
  FROM bids
 GROUP BY listing_id;

输出 1:

listing_id  maxbid
34          95.37
40          103.00
38          507.00
41          94.00
48          76.00
6469        22.00
6472        5.00
37          511.00
31          100.00

查询 2:

SELECT b.listing_id
  FROM bids AS b
  JOIN (SELECT listing_id, MAX(bid) AS maxBid
          FROM bids
         GROUP BY listing_id
       ) AS m
    ON m.listing_id = b.listing_id AND m.maxBid = b.bid
 WHERE b.user_id = 1;

输出 2:

listing_id
34
37
48
6469
6472

查询 3:

SELECT l.end_date, l.user_id, l.title, l.id
  FROM listings AS l
  JOIN (SELECT b.listing_id
          FROM bids AS b
          JOIN (SELECT listing_id, MAX(bid) AS maxBid
                  FROM bids
                 GROUP BY listing_id
               ) AS m
            ON m.listing_id = b.listing_id AND m.maxBid = b.bid
         WHERE b.user_id = 1
       ) AS u
    ON l.id = u.listing_id;

输出 3: end_date user_id title id 2013-01-09 08:11:16 1 圣诞树 6469 2013-01-11 09:17:31 3 另一个测试项目6472

查询 4

SELECT l.end_date, l.user_id, l.title, l.id
  FROM listings AS l
  JOIN (SELECT b.listing_id
          FROM bids AS b
          JOIN (SELECT listing_id, MAX(bid) AS maxBid
                  FROM bids
                 GROUP BY listing_id
               ) AS m
            ON m.listing_id = b.listing_id AND m.maxBid = b.bid
         WHERE b.user_id = 1
       ) AS u
    ON l.id = u.listing_id
 WHERE l.end_date > CURRENT YEAR TO SECOND;

输出 4:

end_date                user_id     title                   id
2013-01-09 08:11:16     1           Christmas Tree          6469
2013-01-11 09:17:31     3           Another test item       6472