mysql - 如何使用 mysql 连接查找每个类别下的产品总数？

Question

我有 2 个表 - 类别和产品。我想获得每个类别下的产品总数？我的表格看起来像:

表格类别

category_id     category_name
1               first category
2               second category
3               third category

表格产品

product_id     product_name     category_id
1              first product    1
2              second product   1
3              third product    1
4              fourth product   3
5              fifth product    3

我想要以下输出:

category_id     category_name     total_products
1               first category    3
2               second category   0
3               third category    2

我目前正在使用以下 sql，但它没有产生正确的结果:

SELECT `c`.`category_id`, `c`.`category_name`, COUNT(`p`.`product_id`) AS total_products FROM `categories` AS `c` INNER JOIN `products` AS `p` ON `c`.`category_id` = `p`.`product_id` GROUP BY `p`.`category_id`

Answer 1

你需要使用左连接作为

select
c.*, coalesce(count(p.category_id),0) as total_products
from categories c
left join products p on p.category_id = c.category_id
group by c.category_id ;