如何编写SQL查询删除BC=B1且NI匹配关联的数据集条目？

需求：删除满足以下条件的数据集条目——当列BC等于B1时，删除该NI对应的所有关联条目。例如示例数据中，NI为BB123456B的3条记录需全部删除。

现有查询语句 #

select mbd.mf, mml.ef, ni, mcb.bc
from m_mc_lg mml
inner join mem_b_det mbd
on mml.mf=mbd.mf
and mml.cf=mbd.cf
inner join mem_e me
on me.mf=mml.mf
and me.cf=mml.cf 
and me.ef=mml.ef
join mem_care_ben mcb
on mcb.mf=mbd.mf
and mcb.cf=mbd.cf 
and mcb.ef=mml.ef

示例数据集 #

解决方案 #

核心逻辑是：先找出所有关联BC='B1'的NI值，再删除这些NI对应的所有记录。

1. 定位目标NI值 #

先执行查询确认需要删除的NI列表：

SELECT DISTINCT ni
FROM m_mc_lg mml
JOIN mem_b_det mbd ON mml.mf = mbd.mf AND mml.cf = mbd.cf
JOIN mem_e me ON me.mf = mml.mf AND me.cf = mml.cf AND me.ef = mml.ef
JOIN mem_care_ben mcb ON mcb.mf = mbd.mf AND mcb.cf = mbd.cf AND mcb.ef = mml.ef
WHERE mcb.bc = 'B1'

2. 删除对应记录 #

根据使用的数据库类型，选择对应的删除语法：

MySQL 多表删除

DELETE mml, mbd, me, mcb
FROM m_mc_lg mml
JOIN mem_b_det mbd ON mml.mf = mbd.mf AND mml.cf = mbd.cf
JOIN mem_e me ON me.mf = mml.mf AND me.cf = mml.cf AND me.ef = mml.ef
JOIN mem_care_ben mcb ON mcb.mf = mbd.mf AND mcb.cf = mbd.cf AND mcb.ef = mml.ef
WHERE mml.ni IN (
    SELECT DISTINCT ni
    FROM m_mc_lg mml_sub
    JOIN mem_b_det mbd_sub ON mml_sub.mf = mbd_sub.mf AND mml_sub.cf = mbd_sub.cf
    JOIN mem_e me_sub ON me_sub.mf = mml_sub.mf AND me_sub.cf = mml_sub.cf AND me_sub.ef = mml_sub.ef
    JOIN mem_care_ben mcb_sub ON mcb_sub.mf = mbd_sub.mf AND mcb_sub.cf = mbd_sub.cf AND mcb_sub.ef = mml_sub.ef
    WHERE mcb_sub.bc = 'B1'
)

PostgreSQL 分步删除（带CTE）

-- 第一步：定义目标NI集合
WITH target_nis AS (
    SELECT DISTINCT ni
    FROM m_mc_lg mml
    JOIN mem_b_det mbd ON mml.mf = mbd.mf AND mml.cf = mbd.cf
    JOIN mem_e me ON me.mf = mml.mf AND me.cf = mml.cf AND me.ef = mml.ef
    JOIN mem_care_ben mcb ON mcb.mf = mbd.mf AND mcb.cf = mbd.cf AND mcb.ef = mml.ef
    WHERE mcb.bc = 'B1'
)
-- 第二步：删除主表记录
DELETE FROM m_mc_lg
WHERE ni IN (SELECT ni FROM target_nis);

-- 第三步：删除关联表记录（根据外键关系调整，示例为mem_b_det）
DELETE FROM mem_b_det
WHERE (mf, cf) IN (
    SELECT mf, cf FROM m_mc_lg WHERE ni IN (SELECT ni FROM target_nis)
);
-- 重复上述逻辑删除mem_e、mem_care_ben中的对应记录

注意事项 #

• 删除前验证：执行删除操作前，先用SELECT语句替换DELETE，确认待删除的记录范围，避免误删。
• 外键约束：如果表之间存在外键关联，需注意删除顺序（先删子表，再删主表），或提前设置外键的ON DELETE CASCADE属性实现级联删除。
• 数据库差异：不同数据库的多表删除语法存在差异，需根据实际使用的数据库调整语句。

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