如何在Mysql Left Join中获取计数

本文关键字：获取 Join Left Mysql | 更新日期: 2023-09-27 18:00:09

我有部门和员工表。模式就像

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

我需要统计每个部门的员工人数。我在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;

但结果集仅包含包含员工的部门。我需要那些没有员工的部门作为零。

如何在Mysql Left Join中获取计数

只需交换表名，

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;

要进一步了解加入，请访问以下链接：

  • SQL联接的可视化表示
select d.depname,count(e.id) from employee as e 
right join department as d on d.depid = e.depid 
group by d.depid;

出于原因，请浏览http://dev.mysql.com/doc/refman/5.0/en/join.html

相关文章：