如何在计数汇总中包含“0”/“0”结果?

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.

我只是有点受困于SQL。我不认为我能很好地表达这个问题，所以让我给你们看看。

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.

我有两个桌子，一个叫人，一个叫约会。我试图返回一个人的预约次数(包括如果他们没有预约的话)。Appointment包含person_id，每个预约有一个person_id。所以COUNT(person_id)是一种明智的方法。

The query:

查询:

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

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拥有的约会数。然而，一个有0个约会的人不会被返回(显然，因为他们不在该表中)。

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

调整语句以从person表中获取person_id给我一些类似的东西:

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;

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 id，而不是我想要的，这是一个有0预约的人的返回!

Any suggestions please?

有什么建议吗?

4 个解决方案

#1

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

您需要为此设置一个外部连接(并且需要使用person作为“driving”表)

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;

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.

之所以这样做，是因为对于那些没有预约的人来说，外部(左)连接将返回NULL。聚合函数count()不会计算空值，因此会得到0。

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

如果您想了解更多关于外部连接的知识，这里有一个很好的教程:http://sqlzoo.net/wiki/Using_Null

#2

You must use LEFT JOIN instead of INNER JOIN

必须使用左连接而不是内连接

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;

#3

if you do the outer join (with the count), and then use this result as a sub-table, you can get 0 as expected (thanks to the nvl function)

如果您执行外部连接(使用计数)，然后使用这个结果作为子表，您可以得到预期的0(感谢nvl函数)

Ex:

例:

select P.person_id, nvl(A.nb_apptmts, 0) from 
(SELECT person.person_id
FROM person) P
LEFT JOIN 
(select person_id, count(*) as nb_apptmts
from appointment 
group by person_id) A
ON P.person_id = A.person_id

#4

USE join to get 0 count in the result using GROUP BY.

使用join使用GROUP BY在结果中获取0计数。

simply 'join' does Inner join in MS SQL so , Go for left or right join.

在MS SQL中，只需要“join”就可以进行内部连接，因此，可以选择左连接或右连接。

If the table which contains the primary key is mentioned first in the QUERY then use LEFT join else RIGHT join.

如果在查询中首先提到包含主键的表，则使用左连接else右连接。

EG:

例如:

select WARDNO,count(WARDCODE) from MAIPADH 
right join  MSWARDH on MSWARDH.WARDNO= MAIPADH.WARDCODE
group by WARDNO

。

select WARDNO,count(WARDCODE) from MSWARDH
left join  MAIPADH on MSWARDH.WARDNO= MAIPADH.WARDCODE group by WARDNO

Take group by from the table which has Primary key and count from the another table which has actual entries/details.

从具有主键的表中取组，从另一个具有实际条目/详细信息的表中取计数。

#1