mysql - 子查询左连接未返回预期内容

Question

出价表中的一行看起来像这样:

        [id] => 108
        [item_id] => 467
        [user_id] => 66
        [bid] => 24
        [bid_date] => 2015-03-19 10:17:12

我想要做的是从 items 表中获取所有项目(正在发生)，同时获取最高出价并计算 bids< 中的出价表。目前，以下查询仅返回一项的最高出价并计算出价。如何更改它以返回每个项目的两个？

"SELECT t1.id, t1.user_id, t1.item_title, t1.item_number, t1.item_start, t1.item_description, t1.active, t2.first_name, t2.last_name, t2.fb_id, b.maxbid, b.bids
            FROM
              items t1
            LEFT JOIN
              users t2
            ON
              t1.user_id = t2.id
            LEFT JOIN (
              SELECT item_id, MAX(bid) AS maxbid, COUNT(bid) AS bids
              FROM bids
            ) b
            ON t1.id = b.item_id
            WHERE t1.active = 1
            ORDER BY id DESC"

Answer 1

使用

group by item_id

在子查询 b 中