如何实现仅当T2中某ID所有行满足条件时删除T1对应行？

场景与示例数据 #

表T1含ID列，表T2含与T1对应的FooID列，二者为1:n映射（T1一个ID对应T2多行），且该列未设外键。示例数据如下：

T1表 #

ID
42
43

T2表 #

FooID | Timestamp
42    | '2023-10-04T07:30:00'
42    | '2023-10-04T08:30:00'
43    | '2023-10-04T07:30:00'

需求 #

仅当T2中同一ID的所有行都满足Timestamp <= '2023-10-04T08:00:00'时，删除T1中对应的行。

当前问题 #

现有SQL语句会误删不符合要求的行：

DELETE foo 
FROM T1 foo
JOIN T2 bar ON foo.ID = bar.FooID
WHERE bar.TimeStamp <= '2023-10-04T08:00:00'

比如ID42，T2中虽有一行满足时间条件，但另一行超时，本不应删除，却被该语句误删。

可行解决方案 #

方案1：排除有超时行的ID #

先筛选出T2中存在超时行的FooID，删除T1中不在此列表的ID：

DELETE FROM T1
WHERE ID NOT IN (
    SELECT DISTINCT FooID
    FROM T2
    WHERE Timestamp > '2023-10-04T08:00:00'
)

逻辑：只要T2中某ID有一行超时，就把该ID排除在删除范围外；剩下的ID就是所有关联行都符合时间条件的，执行删除。

方案2：分组判断最大时间 #

按FooID分组，判断分组内最大时间是否≤阈值，删除T1中符合该条件的ID：

DELETE FROM T1
WHERE ID IN (
    SELECT FooID
    FROM T2
    GROUP BY FooID
    HAVING MAX(Timestamp) <= '2023-10-04T08:00:00'
)

逻辑：如果某FooID对应的所有T2行中，最大的时间都不超过阈值，说明所有行都符合条件，删除T1中对应的ID。

结果验证 #

针对示例数据：

• ID42的T2行存在超时记录，两种方案都不会删除它；
• ID43的所有T2行都符合时间条件，会被正确删除。

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