使用 mysql group by 显示计数为 0 的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/743456/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Displaying rows with count 0 with mysql group by
提问by simplfuzz
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?
怎么做？
回答by Tomalak
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
回答by Erik
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 更改为您的名字
回答by Vedmant
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;
