MySQL:对"WHERE IN“列表中未找到的项返回0的计数

Question

我有一个页面，列出100个拍卖，我想显示每个项目的出价数量。到目前为止(在本例中仅使用4次拍卖)，如下所示：

SELECT itemid, COUNT(*) as count FROM bids WHERE itemid IN(487359,487342,487339,487338) GROUP BY itemid

这显示了出价的数量..。但是，只有在出价表中确实存在出价(和itemid)的情况下。没有出价的项目将不会在投标表中有一个项目。如何为这些返回"0“计数？

我试过以下几种方法，但它们也不起作用：

IFNULL(COUNT(*),0) and COLEASCE(COUNT(*),0)

更新

以下是一些示例数据：

投标表

itemid _ bid _

487359 / 1.00

487359 / 2.00

487359 / 2.50

487342 / 8.20

487338 / 1.00

实际发生了什么：

itemid #投标

487359 x-3

487342 x-1

487338 x-1

我想要的是：

itemid #投标

487359 x-3

487342 x-1

487339 x-0

487338 x-1

请注意，在拍卖表中找不到"487339“项，因为没有记录该项目的出价。我希望mysql为这些返回0。

Answer 1

这可以用OUTER JOIN来完成。

SELECT i.id as itemid, COUNT(DISTINCT b.id) as count
  FROM items i LEFT OUTER JOIN bids b ON i.id = b.itemid
 WHERE i.id IN (487359, 487342, 487339, 487338)
 GROUP BY i.id

这将始终返回指定的项目-只要记录存在-和一个0，如果没有找到出价。由于正在使用OUTER JOIN，如果需要向投标添加任何筛选条件，请确保将其添加到带有AND的OUTER JOIN中。不要添加bids标准到WHERE，否则您的OUTER JOIN将被视为一个INNER JOIN，删除所有记录与0出价。

Answer 2

您可以在这里使用条件求和：

SELECT
    itemid,
    SUM(itemid IN (487359, 487342, 487339, 487338, ...)) AS count
FROM bids
GROUP BY
    itemid;

但是，更好的方法，特别是如果itemid的列表很长，应该将这些值放在单独的表items中，然后使用bids进行左连接。

SELECT
    b.itemid,
    COUNT(i.itemid) AS count
FROM bids b
LEFT JOIN items i            -- contains 487359, 487342, 487339, 487338, ...
    ON i.itemid = b.itemid
GROUP BY
    b.itemid;