PostgreSQL中基于SELECT结果删除指定外的额外账户

问题点分析 #

你当前的代码存在几个核心问题：

1. 普通SQL不支持直接嵌入IF流程控制：IF...END IF属于流程控制语句，只能在存储过程、函数或数据库脚本块（如PostgreSQL的DO块）中使用，不能直接写在普通SELECT/DELETE语句里。
2. 删除语句存在拼写错误：encounttion cascadeerlocation_key是笔误，正确应为encounterlocation_key；另外cascade的用法错误——PostgreSQL中CASCADE是外键约束的属性，不是直接写在DELETE语句后（MySQL同理，需预先设置外键的ON DELETE CASCADE）。
3. 无法直接引用SELECT的别名：DELETE语句不能直接使用SELECT查询里的a、c、l等别名，必须通过关联子查询或CTE传递要删除的主键值。
4. 原SELECT查询逻辑有问题：子查询中使用了d.id_value但未关联personidentifier表，导致无法正确筛选目标病历号的患者。

修正步骤 #

第一步：修正查询，准确定位目标记录 #

先通过CTE（公共表表达式）正确找出需要处理的多账号患者及其所有账号：

WITH patient_multi_accounts AS (
    SELECT 
        a.patient_key,
        d.id_value AS Medical_Record_Number,
        c.id_value AS Account_number,
        c.identifierdomain_key,
        l.encounterlocation_key,
        a.encounter_key
    FROM encounter a
    INNER JOIN personname b ON a.patient_key = b.person_key
    INNER JOIN encounteridentifier c ON a.encounter_key = c.encounter_key
    INNER JOIN personidentifier d ON a.patient_key = d.person_key
    INNER JOIN encounterlocation l ON a.assigned_location = l.encounterlocation_key
    WHERE d.id_value = '000000109' -- 指定目标病历号
)
SELECT * 
FROM patient_multi_accounts
WHERE patient_key IN (
    SELECT patient_key 
    FROM patient_multi_accounts
    GROUP BY patient_key
    HAVING COUNT(*) > 1 -- 筛选拥有多个账号的患者
)
ORDER BY (SELECT family_name FROM personname WHERE person_key = patient_multi_accounts.patient_key);

第二步：实现自动化删除逻辑 #

根据你使用的数据库，选择以下两种方案：

方案1：用CTE批量删除（无需流程控制，直接过滤保留账号）

适合PostgreSQL、SQL Server等支持CTE删除的数据库，直接筛选出要删除的账号后批量操作：

-- PostgreSQL版本
WITH accounts_to_delete AS (
    SELECT 
        c.identifierdomain_key,
        l.encounterlocation_key,
        a.encounter_key
    FROM encounter a
    INNER JOIN encounteridentifier c ON a.encounter_key = c.encounter_key
    INNER JOIN personidentifier d ON a.patient_key = d.person_key
    INNER JOIN encounterlocation l ON a.assigned_location = l.encounterlocation_key
    WHERE d.id_value = '000000109'
      AND a.patient_key IN (
          SELECT patient_key 
          FROM encounter
          GROUP BY patient_key
          HAVING COUNT(*) > 1
      )
      AND c.id_value != '12342' -- 排除需要保留的账号
)
-- 按外键依赖顺序删除，避免约束错误
DELETE FROM encounterlocation
WHERE encounterlocation_key IN (SELECT encounterlocation_key FROM accounts_to_delete);

DELETE FROM encounteridentifier
WHERE identifierdomain_key IN (SELECT identifierdomain_key FROM accounts_to_delete);

DELETE FROM account
WHERE account_key IN (SELECT encounter_key FROM accounts_to_delete);

DELETE FROM encounter
WHERE encounter_key IN (SELECT encounter_key FROM accounts_to_delete);

方案2：用存储过程实现IF逻辑（适合复杂判断）

如果需要更灵活的流程控制，比如多条件判断，可使用存储过程（以MySQL为例）：

DELIMITER //
CREATE PROCEDURE DeleteExtraPatientAccounts()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE acc_num VARCHAR(255);
    DECLARE domain_key INT;
    DECLARE loc_key INT;
    DECLARE acc_key INT;

    -- 游标遍历所有目标账号
    DECLARE cur_accounts CURSOR FOR
        SELECT 
            c.id_value,
            c.identifierdomain_key,
            l.encounterlocation_key,
            a.encounter_key
        FROM encounter a
        INNER JOIN encounteridentifier c ON a.encounter_key = c.encounter_key
        INNER JOIN personidentifier d ON a.patient_key = d.person_key
        INNER JOIN encounterlocation l ON a.assigned_location = l.encounterlocation_key
        WHERE d.id_value = '000000109'
          AND a.patient_key IN (
              SELECT patient_key 
              FROM encounter
              GROUP BY patient_key
              HAVING COUNT(*) > 1
          );

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur_accounts;
    account_loop: LOOP
        FETCH cur_accounts INTO acc_num, domain_key, loc_key, acc_key;
        IF done THEN
            LEAVE account_loop;
        END IF;

        -- 判断是否为需保留的账号
        IF acc_num != '12342' THEN
            -- 按子表到父表的顺序删除
            DELETE FROM encounterlocation WHERE encounterlocation_key = loc_key;
            DELETE FROM encounteridentifier WHERE identifierdomain_key = domain_key;
            DELETE FROM account WHERE account_key = acc_key;
            DELETE FROM encounter WHERE encounter_key = acc_key;
        END IF;
    END LOOP;
    CLOSE cur_accounts;
END //
DELIMITER ;

-- 调用存储过程执行删除
CALL DeleteExtraPatientAccounts();

注意事项 #

• 先验证后删除：执行删除前，务必先用SELECT语句确认要删除的记录是否正确，避免误删数据。
• 外键约束处理：删除顺序必须从依赖表（子表）到主表，否则会因外键关联导致删除失败。
• 数据库适配：不同数据库语法略有差异，比如PostgreSQL的DO块、SQL Server的存储过程语法，需根据实际环境调整。

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