sql - SQL多表联接子查询

如何仅对invite表使用子查询？我想要patient_profiles中的所有记录，并且对于邀请join，只使用在特定日期之后创建的记录？

SELECT p.first_name,
COUNT(invites.invited_by_id)as invite_count
FROM patient_profiles AS p
LEFT OUTER JOIN patient_profiles AS invites ON invites.invited_by_id = p.id
WHERE p.is_test = false AND AND invites.created_at >= '2017-10-16'::date
GROUP BY p.first_name

最佳答案

不需要子查询。只需将日期条件移到ON子句：

SELECT p.first_name,
       COUNT(invites.invited_by_id)as invite_count
FROM patient_profiles p LEFT OUTER JOIN
     patient_profiles invite
     ON invites.invited_by_id = p.id AND invites.created_at >= '2017-10-16'::date
WHERE p.is_test = false
GROUP BY p.first_name;