MySQL：多表LEFT JOIN后按唯一ID统计指定条件记录数

问题根源 #

原SQL的子查询未关联主表referral的reference字段，导致它计算的是所有measure表中status_calculation <> 'Cancelled'的总记录数，而非按每个r.reference分组统计。

方案一：关联子查询（保留子查询写法） #

在子查询中添加与主表的关联条件，确保每个r.reference仅统计对应的measure记录：

SELECT r.reference, r.status, pcla.la_name, r.project_reference, 
       (SELECT COUNT(DISTINCT(id))
        FROM measure m_sub
        WHERE m_sub.reference = r.reference
          AND m_sub.status_calculation <> 'Cancelled'
          AND m_sub.completed IS NOT NULL) AS Test,
       p.EPCR_GasProperty, DATE(r.created)
FROM referral r
LEFT JOIN postcode_la pcla ON r.postcode = pcla.postcode
LEFT JOIN customer c ON r.reference = c.reference
LEFT JOIN survey s ON s.customer_id = c.id
LEFT JOIN property p ON p.id = s.property_id
WHERE DATE(r.created) > '2022-03-31'
  AND pcla.la_name = 'Ealing'
GROUP BY r.reference, r.status, pcla.la_name, r.project_reference, p.EPCR_GasProperty, DATE(r.created)
ORDER BY r.reference
LIMIT 20;

方案二：利用JOIN与条件聚合（更高效） #

借助已关联的measure表，用COUNT(CASE...)直接做条件统计，避免子查询：

SELECT r.reference, r.status, pcla.la_name, r.project_reference, 
       COUNT(DISTINCT CASE WHEN m.status_calculation <> 'Cancelled' AND m.completed IS NOT NULL THEN m.id END) AS Test,
       p.EPCR_GasProperty, DATE(r.created)
FROM referral r
LEFT JOIN postcode_la pcla ON r.postcode = pcla.postcode
LEFT JOIN measure m ON r.reference = m.reference
LEFT JOIN customer c ON r.reference = c.reference
LEFT JOIN survey s ON s.customer_id = c.id
LEFT JOIN property p ON p.id = s.property_id
WHERE DATE(r.created) > '2022-03-31'
  AND pcla.la_name = 'Ealing'
GROUP BY r.reference, r.status, pcla.la_name, r.project_reference, p.EPCR_GasProperty, DATE(r.created)
ORDER BY r.reference
LIMIT 20;

额外说明 #

1. 原SQL将DATE(r.created) > '2022-03-31'、pcla.la_name = 'Ealing'放在JOIN的AND条件中，会把过滤逻辑变成JOIN时的匹配条件而非全局过滤，因此移至WHERE子句更符合需求。
2. 分组时需显式列出SELECT中所有非聚合字段（不同SQL模式要求不同，显式列出可避免语法错误）。

内容的提问来源于stack exchange，提问作者MariaT