基于科室收费标记就诊记录：双表关联去重的实现需求

我有两张不同粒度的表：

• 就诊记录表（Encounter Table）：每个就诊ID（encounter id）对应一行数据
• 收费记录表（Charge Table）：每个就诊ID对应多行数据，每行对应不同科室（Department）

需求是将两张表关联，确保每个就诊ID仅出现一行，同时为每个就诊ID标记是否存在至少1条a、b、c科室的收费记录。

现有收费记录表数据：

期望结果：

我之前尝试直接通过就诊ID关联两张表，并用CASE表达式判断：

CASE 
WHEN Department = 'a' THEN 1 ELSE 0
END

但因为每个就诊ID对应多条收费记录，结果生成了重复的就诊行。

问题核心是未对收费表按就诊ID做聚合，直接关联会保留收费表的多行数据，导致就诊ID重复。以下两种方法可以解决这个问题：

方法1：先聚合收费表，再关联就诊表 #

先对收费表按就诊ID分组，用MAX(CASE...)标记是否存在对应科室（只要有一条符合记录，MAX就会返回1，否则0），再用LEFT JOIN关联就诊表，确保所有就诊ID都被保留：

SELECT
    e.就诊ID,
    COALESCE(c.标记a, 0) AS 标记a,
    COALESCE(c.标记b, 0) AS 标记b,
    COALESCE(c.标记c, 0) AS 标记c
FROM
    Encounter e
LEFT JOIN (
    SELECT
        就诊ID,
        MAX(CASE WHEN Department = 'a' THEN 1 ELSE 0 END) AS 标记a,
        MAX(CASE WHEN Department = 'b' THEN 1 ELSE 0 END) AS 标记b,
        MAX(CASE WHEN Department = 'c' THEN 1 ELSE 0 END) AS 标记c
    FROM
        Charge
    GROUP BY
        就诊ID
) c ON e.就诊ID = c.就诊ID;

方法2：用EXISTS子查询直接标记 #

如果不需要关联太多字段，也可以直接在就诊表中用EXISTS判断每个科室是否存在对应收费记录，结果行数和就诊表完全一致，不会产生重复：

SELECT
    就诊ID,
    CASE WHEN EXISTS (SELECT 1 FROM Charge c WHERE c.就诊ID = e.就诊ID AND c.Department = 'a') THEN 1 ELSE 0 END AS 标记a,
    CASE WHEN EXISTS (SELECT 1 FROM Charge c WHERE c.就诊ID = e.就诊ID AND c.Department = 'b') THEN 1 ELSE 0 END AS 标记b,
    CASE WHEN EXISTS (SELECT 1 FROM Charge c WHERE c.就诊ID = e.就诊ID AND c.Department = 'c') THEN 1 ELSE 0 END AS 标记c
FROM
    Encounter e;

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