如何实现多次INNER JOIN与GROUP BY，合并多条医疗统计SQL查询

完全可以合并，需要注意不能直接将三张业务表和机构表关联后统一聚合，否则会产生笛卡尔积，导致统计的人数、剂次、预约数远大于真实值。正确的做法是先单独聚合每个维度的统计值，再和机构基础表关联。

原SQL优化建议 #

• 删除SELECT语句中重复查询的phone字段
• 调整聚合逻辑：先按机构维度统计业务指标，再统一关联机构的基础属性（地址、电话等），避免每个子查询重复查机构属性，执行效率更高

兼容低版本数据库的子查询写法 #

适用于MySQL 5.x等不支持CTE的数据库：

SELECT 
  f.`name`,
  f.address,
  f.phone,
  f.type,
  f.capacity,
  -- 处理空值，无数据时显示为0
  COALESCE(w.totalWorkers, 0) AS totalWorkers,
  COALESCE(v.totalDose, 0) AS totalDose,
  COALESCE(b.totalBookings, 0) AS totalBookings
FROM facility f
-- 关联公共卫生工作者统计结果
LEFT JOIN (
  SELECT 
    facility_name,
    COUNT(person_id) AS totalWorkers
  FROM healthcare_worker
  GROUP BY facility_name
) w ON f.`name` = w.facility_name
-- 关联累计接种剂次统计结果
LEFT JOIN (
  SELECT 
    location,
    SUM(dose) AS totalDose
  FROM vaccination
  GROUP BY location
) v ON f.`name` = v.location
-- 关联未来预约剂次统计结果
LEFT JOIN (
  SELECT 
    facility_name,
    COUNT(booking_id) AS totalBookings
  FROM booking
  GROUP BY facility_name
) b ON f.`name` = b.facility_name;

高可读性CTE写法 #

适用于MySQL 8.0、PostgreSQL、SQL Server等支持公共表表达式的数据库：

WITH worker_stats AS (
  SELECT facility_name, COUNT(person_id) AS totalWorkers
  FROM healthcare_worker
  GROUP BY facility_name
),
vaccine_stats AS (
  SELECT location, SUM(dose) AS totalDose
  FROM vaccination
  GROUP BY location
),
booking_stats AS (
  SELECT facility_name, COUNT(booking_id) AS totalBookings
  FROM booking
  GROUP BY facility_name
)
SELECT 
  f.`name`,
  f.address,
  f.phone,
  f.type,
  f.capacity,
  COALESCE(w.totalWorkers, 0) AS totalWorkers,
  COALESCE(v.totalDose, 0) AS totalDose,
  COALESCE(b.totalBookings, 0) AS totalBookings
FROM facility f
LEFT JOIN worker_stats w ON f.`name` = w.facility_name
LEFT JOIN vaccine_stats v ON f.`name` = v.location
LEFT JOIN booking_stats b ON f.`name` = b.facility_name;

注意事项 #

• 若机构表存在name重复的情况，建议改用机构唯一ID作为关联键，避免统计数据错位
• 若需要过滤特定类型/区域的机构，直接在主查询末尾加WHERE条件即可，不需要在每个子查询中重复过滤
• 不需要保留无数据的机构时，将LEFT JOIN改为INNER JOIN即可

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