如何包含“零"/“0"结果在 COUNT 聚合? [英] How to include "zero" / "0" results in COUNT aggregate?

问题描述

我刚刚被一些 SQL 困住了.我不认为我可以巧妙地表达这个问题 - 所以让我向你展示.

I've just got myself a little bit stuck with some SQL. I don't think I can phrase the question brilliantly - so let me show you.

我有两张桌子，一张叫人，一张叫约会.我正在尝试返回一个人的约会次数(包括他们是否为零).约会包含 person_id 并且每个约会都有一个 person_id.所以 COUNT(person_id) 是一个明智的方法.

I have two tables, one called person, one called appointment. I'm trying to return the number of appointments a person has (including if they have zero). Appointment contains the person_id and there is a person_id per appointment. So COUNT(person_id) is a sensible approach.

查询:

SELECT person_id, COUNT(person_id) AS "number_of_appointments" 
FROM appointment 
GROUP BY person_id;

将正确返回一个 person_id 的约会次数.但是，有 0 个约会的人不会返回(显然因为他们不在该表中).

Will return correctly, the number of appointments a person_id has. However, a person who has 0 appointments isn't returned (obviously as they are not in that table).

调整语句以从 person 表中获取 person_id 给了我类似的结果:

Tweaking the statement to take person_id from the person table gives me something like:

SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments"
FROM appointment
JOIN person ON person.person_id = appointment.person_id
GROUP BY person.person_id;

然而，这仍然只会返回一个有约会的 person_id 而不是我想要的，这是一个有 0 个约会的人的回报！

This however, will still only return a person_id who has an appointment and not what I want which is a return with persons who have 0 appointments!

请问有什么建议吗?

推荐答案

你需要一个外连接(你需要使用 person 作为驱动"表)

You want an outer join for this (and you need to use person as the "driving" table)

SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments"
FROM person 
  LEFT JOIN appointment ON person.person_id = appointment.person_id
GROUP BY person.person_id;

之所以有效，是因为外部(左)联接将为那些没有预约的人返回 NULL.聚合函数 count() 不会计算 NULL 值，因此您将得到零.

The reason why this is working, is that the outer (left) join will return NULL for those persons that do not have an appointment. The aggregate function count() will not count NULL values and thus you'll get a zero.

如果您想了解有关外连接的更多信息，这里有一个不错的教程:http://sqlzoo.net/维基/Using_Null

If you want to learn more about outer joins, here is a nice tutorial: http://sqlzoo.net/wiki/Using_Null

这篇关于如何包含“零"/“0"结果在 COUNT 聚合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！