简单的SQL内部连接查询-无法工作

Question

    SELECT ai.auction_item_id, ai.starting_bid, b.bid_amount, i.*
    FROM
    auction_item as ai, //Table contains auction specific details about the item
    item as i //Table contains general details about the item
    INNER JOIN (
            SELECT auction_item_id, bid_amount
            FROM bid xb //Table contains bids on item
            ORDER BY amount DESC
            LIMIT 1 ) b 
     ON b.auction_item_id = ai.auction_item_id
    WHERE
    ai.auction_id = 4 AND
    i.id = ai.listings_id

    ORDER BY RAND()
    LIMIT 4

目前，通过上述查询，我可以从当前的活动拍卖(拍卖#4)中获得4个随机项目。但他们现在都是以相同的最高出价(从内部加入)回来。每个项目都应该有自己的最高出价金额(从内部加入)-除非该项目没有出价，那么它应该是0(或什么)。

我在这里做错什么了？bid表对每个出价都有一行，因此在内部联接中设置了限制1和desc命令，因为我希望外层部分中的每个项目都有最高的出价。(如果有投标的话)

谢谢

Answer 1

尝试一个子查询。如果由于0的缘故，在出价表中没有条目，您将得到一个coalesce。

SELECT 
    ai.auction_item_id, ai.starting_bid, 
    Coalesce((SELECT max(xb.bid_amount)
              FROM bid xb
              WHERE xb.auction_item_id = ai.auction_item_id), 0) AS bid_amount,
    i.*
FROM auction_item AS ai
INNER JOIN item AS i ON i.id = ai.listings_id
WHERE ai.auction_id = 4 
ORDER BY Rand()

Answer 2

像这样吗？

SELECT ai.auction_item_id, ai.starting_bid, b.bid_amount, max(bid_amount)
    FROM auction_item as ai
      inner join bid b
        on ai.auction_item_id = b.auction_item_id
      inner join items i
        on i.id = ai.listings_id
    where ai.auction_id = 4

将表架构添加到问题中将有帮助。

编辑

SELECT ai.auction_item_id, ai.starting_bid, IFNULL(max(bid_amount), 0) max_bid
        FROM auction_item as ai
          left join bid b
            on ai.auction_item_id = b.auction_item_id
          inner join items i
            on i.id = ai.listings_id
        WHERE ai.auction_id = 4
        GROUP BY ai.auction_item_id

给你，包括没有出价的项目。

如果我弄错了，请告诉我：http://sqlfiddle.com/#!9/0eae7/2