执行Outer Join时未获取全部预期数据的技术求助

需求与问题现状 #

需要获取所有医院的三类约束（Physical Restraint、Seclusion、Mechanical Restraint）的计数，要求每个医院对应三类约束都显示，无记录时显示NULL。

期望结果 #

实际结果 #

问题根源 #

你的SQL连接逻辑顺序错误：

• 原代码先左连接tblHolds，再用INNER JOIN关联tblHospitalList，但当tblHolds中无对应约束记录时，b.hosp_id为NULL，INNER JOIN会直接过滤掉这些医院-约束组合，导致缺失无记录的行。
• 另外，临时表#interventions的插入逻辑依赖tblHolds中的数据，如果某类约束在tblHolds中完全没有记录，临时表就会遗漏该类型，无法满足全量展示要求。

修正后的SQL代码 #

CREATE TABLE #interventions (
  intervention varchar(100)
)

-- 直接插入需要的三类约束，避免依赖tblHolds中的数据
INSERT INTO #interventions 
VALUES ('Physical Restraint'), ('Seclusion'), ('Mechanical Restraint')

-- 先生成所有医院+所有约束类型的完整组合，再左连接统计
SELECT 
  c.hosp_name AS hospital, 
  a.intervention, 
  -- 无记录时返回NULL，有记录时返回计数
  CASE WHEN COUNT(b.intervention) = 0 THEN NULL ELSE COUNT(b.intervention) END AS intervention_cnt
FROM tblHospitalList c
CROSS JOIN #interventions a  -- 交叉连接得到所有医院-约束组合
LEFT JOIN tblHolds b 
  ON c.hosp_id = b.hosp_id 
  AND a.intervention = b.intervention  -- 同时关联医院ID和约束类型
GROUP BY c.hosp_name, a.intervention
ORDER BY 
  c.hosp_name,
  CASE a.intervention
    WHEN 'Physical Restraint' THEN 1
    WHEN 'Seclusion' THEN 2
    WHEN 'Mechanical Restraint' THEN 3
    ELSE 9
  END

DROP TABLE #interventions

关键调整说明 #

1. 固定临时表数据：直接插入三类约束名称，确保无论tblHolds中是否有对应记录，都能生成全量的约束类型行。
2. 调整连接顺序：先通过CROSS JOIN生成所有医院与约束类型的笛卡尔积，保证每个医院都有三类约束的基础行。
3. 左连接关联条件：将医院ID和约束类型作为左连接的关联条件，确保即使tblHolds中无对应记录，基础行也不会被过滤。
4. 计数转换：用CASE语句将计数为0的结果转为NULL，匹配预期输出格式。

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