mysql左连接计数总是返回1

这是我的数据结构:

categories 
id   name
-------------------
1    category1
2    category2
3    category3


items
id    name    cat
-------------------
1     item1   1
2     item2   1
3     item3   1
4     item4   2

期望的输出:

cat   category    total_items
-----------------------------------
1     category1   3
2     category2   1
3     category3   0

我尝试了以下查询:

select categories.id as cat, 
    categories.name as category, 
    count(*) AS total_items from categories 
    left join items on categories.id = items.cat

并且它总是会返回1类别3 ..任何想法有什么问题？

试试这个:

select categories.id as cat, categories.name as category, 
       count(items.cat) AS total_items 
from categories 
left join items on categories.id = items.cat

您的查询的问题是COUNT(*)基于行计数,包括表中包含NULL-valued字段的行items.

count(items.cat)相反,使用叶子NULL值的字段.