postgreSQL join subquery where condition

Question

如何有效地对LEFT OUTER JOIN进行子查询，以便仅包含连接中满足特定条件的行？

我只想计算 PPPDconverted_at IS NULL的地方。但是，当我添加 PPPD.converted_at IS NULL 时，结果比我希望的更有限，因为它只包括converted_at中确实有一行 null 的patient_profiles。相反，我想要计算所有具有 converted_at = null 的 PPPD 记录

SELECT P.id, P.gender, P.dob, 
count(distinct recommendations.id) AS recommendation_count,
count(distinct PPPD.id) AS community_submissions,
FROM patient_profiles AS P
LEFT OUTER JOIN recommendations ON recommendations.patient_profile_id = P.id
LEFT OUTER JOIN patient_profile_potential_doctors AS PPPD ON PPPD.patient_profile_id = P.id
WHERE P.is_test = FALSE
GROUP BY P.id

Answer 1

您需要在ON子句中添加条件：

SELECT P.id, P.gender, P.dob, 
count(distinct r.id) AS recommendation_count,
count(distinct PPPD.id) AS community_submissions,
FROM patient_profiles  P LEFT OUTER JOIN
recommendations r
ON r.patient_profile_id = P.id LEFT OUTER JOIN
patient_profile_potential_doctors PPPD
ON PPPD.patient_profile_id = P.id AND PPPD.converted_at IS NULL
WHERE P.is_test = FALSE;
GROUP BY P.id