SQL LEFT JOIN 与 COUNT(*) 问题

Question

我有以下查询：

SELECT products_categories.categoryID, name, COUNT(*) AS itemCount
FROM products_categories
LEFT JOIN products_to_categories ON products_to_categories.categoryID = products_categories.categoryID
GROUP BY products_categories.categoryID

但仍然存在问题：其中没有产品的类别返回 itemCount = 1 而不是 0。我该如何解决这个问题？

Answer 1

尝试 COUNT(product_name) 或其他任何内容，而不是 COUNT(*)。

Answer 2

COUNT(products_to_categories.categoryID)

请求 COUNT(*) 至少会得到 1，因为毕竟有 1 行。具体计数需要具体处理。

Answer 3

SELECT products_categories.categoryID, name, COUNT(*) AS itemCount
FROM products_categories
LEFT JOIN products_to_categories ON products_to_categories.categoryID = products_categories.categoryID
GROUP BY products_categories.categoryID
WHERE itemCount <> '0'

Answer 4

您是否尝试过 COUNT(products_to_categories.categoryID) AS itemCount？我不太确定，但认为问题可能出在 COUNT(*) 上。