如何包括“零"字样? /"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_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/wiki/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屋！