如何基于指定cp.id关联删除对应job_id下符合条件的lu表记录

问题原因分析 #

你写的SQL有两个核心问题：

• 使用USING cp但没有加关联限制，会产生笛卡尔积，匹配到大量无关的cp记录，导致误删
• 逻辑不全：没有先通过输入的cp.id拿到对应的job_id，也没有筛选出该job_id下的所有cp记录，关联链路是断的

正确实现SQL #

版本1：完全匹配需求描述（同job_id下所有cp对应的lu记录，输入cp.id=1时删除6条）

DELETE FROM lu
WHERE EXISTS (
    SELECT 1
    -- 先拿到输入的cp记录对应的job_id
    FROM cp input_cp
    -- 关联拿到同job_id下的所有cp记录
    JOIN cp same_job_cp ON same_job_cp.job_id = input_cp.job_id
    -- 关联qr表拿到对应objectguid
    JOIN qr ON qr.checkpoint_id = same_job_cp.id
    -- 匹配lu的过滤条件
    WHERE input_cp.id = 1 -- 此处替换为你要输入的cp.id
      AND qr.objectguid = lu.objectguid
      AND same_job_cp.domain_id = lu.domain_id
);

版本2：匹配你给出的示例预期（输入cp.id=1时删除4条，额外限制domain_id和输入cp相同）

如果你预期仅删除和输入cp同job_id且同domain_id的lu记录，可以增加domain_id的匹配限制：

DELETE FROM lu
WHERE EXISTS (
    SELECT 1
    FROM cp input_cp
    JOIN cp same_job_cp ON same_job_cp.job_id = input_cp.job_id
    JOIN qr ON qr.checkpoint_id = same_job_cp.id
    WHERE input_cp.id = 1
      AND same_job_cp.domain_id = input_cp.domain_id -- 新增domain_id匹配限制
      AND qr.objectguid = lu.objectguid
      AND same_job_cp.domain_id = lu.domain_id
);

验证说明 #

拿你提供的测试数据测试版本2，输入cp.id=1时，会匹配到job_id=1且domain_id=1的cp记录id=1、2，对应qr的objectguid为637779d2-6d43-4639-b427-5323aa2a0111和637779d2-6d43-4639-b427-5323aa2a0211，对应lu表中正好4条记录，完全符合你的预期。

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