连锁药房：如何筛选未关联处方的重复空患者记录？

需求说明 #

需要定位指定分店（示例中为Branch=9）中满足以下条件的患者记录，这些是可删除的重复空患者：

• 按**姓氏(Surname)、名字(Forenames)、出生日期(DoB)、邮编(Postcode)**判定为重复记录
• 从未开具过处方（PatientId未出现在Script表中）

原查询的问题 #

1. 未排除有处方的患者：原CTE仅筛选了重复组，但包含了已开处方的患者，不符合"空患者"要求
2. NULL值匹配失效：如果患者的姓名、生日或邮编为NULL，原JOIN条件的=比较会返回未知，导致这类重复记录无法被匹配，结果不全

修正后的查询方案 #

方案1：获取所有符合条件的重复患者记录 #

WITH DuplicatePatients AS (
    -- 先筛选指定分店中无处方且重复的患者组
    SELECT 
        p.Surname, 
        p.Forenames, 
        p.DoB, 
        p.Postcode,
        COUNT(*) AS RecordCount
    FROM pmr.Patient p
    WHERE p.Branch = 9
      -- 排除有处方的患者
      AND NOT EXISTS (
          SELECT 1 
          FROM pmr.Script s 
          WHERE s.PatientId = p.PatientId
      )
    GROUP BY p.Surname, p.Forenames, p.DoB, p.Postcode
    HAVING COUNT(*) > 1
)
-- 关联原表获取重复组内的患者详细信息
SELECT 
    p.PatientId,
    p.CardId,
    p.Surname,
    p.Forenames,
    p.DoB,
    p.Postcode
FROM pmr.Patient p
INNER JOIN DuplicatePatients dp 
    -- 用ISNULL处理NULL值，确保空值记录能正确匹配
    ON ISNULL(p.Surname, '') = ISNULL(dp.Surname, '')
    AND ISNULL(p.Forenames, '') = ISNULL(dp.Forenames, '')
    AND ISNULL(p.DoB, '1900-01-01') = ISNULL(dp.DoB, '1900-01-01')
    AND ISNULL(p.Postcode, '') = ISNULL(dp.Postcode, '')
WHERE p.Branch = 9
  AND NOT EXISTS (
      SELECT 1 
      FROM pmr.Script s 
      WHERE s.PatientId = p.PatientId
  )
ORDER BY p.Surname, p.Forenames, p.PatientId;

方案2：标记需保留/删除的记录（推荐） #

如果需要明确区分保留记录（比如保留PatientId最小的）和删除记录，可使用以下查询：

WITH PatientCTE AS (
    SELECT 
        p.PatientId,
        p.CardId,
        p.Surname,
        p.Forenames,
        p.DoB,
        p.Postcode,
        -- 按重复组编号，每组内PatientId最小的记录编号为1
        ROW_NUMBER() OVER (
            PARTITION BY p.Surname, p.Forenames, p.DoB, p.Postcode 
            ORDER BY p.PatientId
        ) AS RowNum
    FROM pmr.Patient p
    WHERE p.Branch = 9
      AND NOT EXISTS (
          SELECT 1 FROM pmr.Script s WHERE s.PatientId = p.PatientId
      )
)
SELECT 
    PatientId,
    CardId,
    Surname,
    Forenames,
    DoB,
    Postcode,
    CASE WHEN RowNum > 1 THEN '需删除' ELSE '保留' END AS Status
FROM PatientCTE
-- 仅筛选存在重复的患者组
WHERE EXISTS (
    SELECT 1 
    FROM PatientCTE pc2
    WHERE pc2.Surname = PatientCTE.Surname
      AND pc2.Forenames = PatientCTE.Forenames
      AND pc2.DoB = PatientCTE.DoB
      AND pc2.Postcode = PatientCTE.Postcode
    GROUP BY pc2.Surname, pc2.Forenames, pc2.DoB, pc2.Postcode
    HAVING COUNT(*) > 1
)
ORDER BY Surname, Forenames, RowNum;

关键优化点 #

• 无处方过滤：通过NOT EXISTS子句精准排除所有有处方记录的患者
• NULL值处理：用ISNULL()将NULL转换为统一默认值，解决空字段无法匹配的问题
• 重复组标记：方案2中的ROW_NUMBER()函数快速区分每组内的保留/删除记录，降低误删风险

内容的提问来源于stack exchange，提问作者Colin-G-Davidson