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(或其他价格)
我在这里做错了什么?投标表中的每个投标都有一行,因此在内部联接中为LIMIT 1和desc排序,因为我想要外部的每个项目的最高投标。(如果有一个出价)。
谢谢
尝试子查询。您geht一个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()
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句