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

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

我在MySql公司有两个表:( cname，city）有效:( ename，cname，salary）

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

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

for e.g. for

例如对于

Company : 
Microsoft Bangalore
IBM       NY

works : 
emp1 Microsoft 10000
emp2 Microsoft 90000

output should be :

输出应该是：

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.

如果我使用外部联接，那么零雇员的公司仍然会出现在一行中，因此该选项也是如此。

How to do it?

怎么做？

3 个解决方案

#1

Classic case for a LEFT JOIN:

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

#2

Try

尝试

Select company.cname, count(work.id) from company left join work on ....=.... group by company.cname

where you fill out the "...." parts, and change the work.id to you name

在哪里填写“....”部分，并将work.id更改为您的名字

#3

There is another way to do so with subquery, here is a sample from my case:

使用子查询还有另一种方法，这是我的案例中的一个示例：

select count(`order_id`) as cnt
from (
    select `order_id` from `room_bookings`
    where `room_id` = 3 and `day_id` = 20180201 
    group by `order_id`
) as b;

#1