如何过滤删除ref对应cost全为零的数据行？

现有数据表结构及初始化数据如下：

CREATE TABLE my_table
(
    cost decimal(10,2),
    ref int
);

INSERT INTO my_table(cost, ref)
VALUES      (0.00, 1),
            (0.50, 1),
            (-1.89, 1),
            (0.00, 2),
            (0.00, 2),
            (0.00, 2),
            (1.23, 3),
            (-9.47, 3),
            (111.23, 3),
            (12.00, 3);

需求是删除ref = 2的所有行，因为该ref对应的所有cost值均为0。

已经通过以下语句筛选出至少存在一行cost不为0的ref，并存入了中间表refs_to_keep：

SELECT DISTINCT ref
INTO            refs_to_keep
FROM            my_table
WHERE           cost != 0.00;

现在需要完成删除操作，但不知道WHERE ref NOT IN针对中间表的正确语法，同时想了解是否有无需中间表的实现方式。

1. 使用中间表的正确删除语法 #

直接将NOT IN后的部分改为子查询，引用中间表的ref字段即可，语法如下：

DELETE FROM my_table
WHERE ref NOT IN (SELECT ref FROM refs_to_keep);

2. 无需中间表的简化实现 #

不需要创建临时表，直接用子查询就能一步完成需求，这里提供几种常用写法：

写法一：利用EXISTS子查询 #

通过关联子查询判断当前ref是否存在非0的cost值，不存在则删除该行：

DELETE FROM my_table
WHERE NOT EXISTS (
    SELECT 1
    FROM my_table t2
    WHERE t2.ref = my_table.ref
      AND t2.cost != 0.00
);

写法二：直接嵌入保留ref的筛选逻辑 #

把筛选需保留ref的逻辑直接写入删除语句的NOT IN子查询中：

DELETE FROM my_table
WHERE ref NOT IN (
    SELECT DISTINCT ref
    FROM my_table
    WHERE cost != 0.00
);

写法三：分组筛选全0ref（适用于支持窗口函数的数据库） #

如果你的数据库支持窗口函数（如MySQL 8+、PostgreSQL、SQL Server等），可以先分组找出所有cost全为0的ref，再删除这些ref对应的行：

DELETE FROM my_table
WHERE ref IN (
    SELECT ref
    FROM (
        SELECT ref,
               MAX(ABS(cost)) AS max_cost -- 用ABS避免负0的特殊情况
        FROM my_table
        GROUP BY ref
        HAVING max_cost = 0.00
    ) AS zero_refs
);

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