mysql - 获取囚犯访客计数

Question

表格看起来像这样:

Person
first_name
middle_name
last_name
person_ssn

Prisoner
person_ssn
prisoner_id

Visitation
visitor_id
prisoner_id

visitor
visitor_id
first_name
last_name

我有兴趣了解囚犯的姓名以及他们有多少访客。

到目前为止我所得到的:

select person.first_name, person.middle_name, person.last_name, count(visitor_id) AS visitorCount
from prisoner
inner join person on prisoner.person_ssn = person.person_ssn
inner join visitation on prisoner.prisoner_id = visitation.prisoner_id;

这似乎只会导致一名囚犯

Answer 1

您需要一个group by子句。否则，您的查询是返回一行的聚合查询。

select person.first_name, person.middle_name, person.last_name, count(visitor_id) AS visitorCount
from prisoner
inner join person on prisoner.person_ssn = person.person_ssn
inner join visitation on prisoner.prisoner_id = visitation.prisoner_id
group by person.first_name, person.middle_name, person.last_name;