使用 mysql group by 显示计数为 0 的行

2021-12-27 00:00:00 group-by mysql

我在 MySql 中有两个表公司 : (cname,city)作品 : (ename,cname,salary)

I have two tables in MySql Company : (cname,city) works : (ename,cname,salary)

我想显示为每家公司工作的员工人数，即使这个数字为零.

I want to display number of employees working for every company, even if that number is zero.

例如对于

Company : 
Microsoft Bangalore
IBM       NY

works : 
emp1 Microsoft 10000
emp2 Microsoft 90000

输出应该是:

Microsoft 2
IBM 0

但以下查询和其他类似查询仅打印那些至少拥有一名员工的公司:

But the following query and other similar queries print only those companies which have at least one employee :

Select count(*) from works natural join company group by company.cname

如果我使用外连接，那么员工人数为零的公司仍然会显示排成一排，因此该选项也已退出.

If I use outer join, then the companies with zero employees will still show up in one row, so that option is out as well.

怎么做?

推荐答案

LEFT JOIN 的经典案例:

Classic case for a LEFT JOIN:

SELECT
  c.cname,
  COUNT(w.ename) wcount 
FROM
  company c
  LEFT JOIN works w ON c.cname = w.cname
GROUP BY
  c.cname

相关文章