mysql - 计数频率还显示 "0"值

Question

我想对一个主要类别的频率进行计数和排序(有关表结构，请参阅 SQLFiddle)。但我还想显示“0”值，因此如果产品未分配categoryId，则该categoryId 的频率应为“0”。

我当前的 SQL 如下所示。

SELECT 
    category.categoryId,
    category.name,
    COUNT(*) AS frequency
FROM
    Categories category
        LEFT JOIN
    Product entry ON entry.categoryId = category.categoryId
WHERE
    category.parentId = 1
GROUP BY category.categoryId
ORDER BY COUNT(*) DESC

结果

| categoryId |             name | frequency |
|------------|------------------|-----------|
|          2 | Sub Category 1-2 |         3 |
|          4 | Sub Category 1-4 |         1 |
|          3 | Sub Category 1-3 |         1 |

如果我进行RIGHT JOIN，则尚未分配的类别将根本​​不会显示(但我需要在结果中显示它)。

我需要的结果应该是这样的:

| categoryId |             name | frequency |
|------------|------------------|-----------|
|          2 | Sub Category 1-2 |         3 |
|          4 | Sub Category 1-4 |         1 |
|          3 | Sub Category 1-3 |         0 |

有没有办法像上面的结果一样显示“0”频率？

SQLFiddle

Answer 1

您需要执行count(entity.categoryId)

SELECT 
    c.categoryId,
    c.name,
    COUNT(e.categoryId) AS frequency
FROM
    Categories c
        LEFT JOIN
    Product e ON e.categoryId = c.categoryId
WHERE
    c.parentId = 1
GROUP BY c.categoryId
ORDER BY frequency DESC