c# - 如何在 Mysql Left Join 中获取计数？

Question

我有表格 department 和 employee。架构就像，

department(id, name)
employee(id,depid,salary)

我需要统计每个部门的员 worker 数。我在 mysql 中的查询就像

select d.depname,count(e.id) 
from employe as e left join department as d on d.depid 
where e.depid=d.depid group by d.depid;

但结果集只包含有员工的部门。我需要那些员工为 0 的部门本身为零。

Answer 1

只是互换表名，

SELECT  d.name, 
        COUNT(e.id) totalEmployeeCount
FROM    department AS d 
        LEFT JOIN employee AS e 
            ON e.depid = d.id
GROUP   BY d.id;

要进一步了解有关连接的更多信息，请访问以下链接:

• > Visual Representation of SQL Joins