如何高效删除表中指定列组全为0或NULL的数据行？

我有一张包含30列的表，需要删除指定列子集内所有值均为0或NULL的数据行。目前用的SQL写法扩展性很差，要重复写很多条件，想找更优方案：

DELETE 
FROM Mytable
Where (col1 is NULL OR col1 = 0  ) AND
(col3 is NULL OR col1 = 0  ) AND
(col5 is NULL OR col1 = 0  )
... -- 大量重复行

示例表（简化列数） #

指定列子集为{col2,col3,col4,col5,col6}，预期删除后仅保留R1、R4、R5行，因为R2、R3的指定列全为0或NULL。

方法1：利用GREATEST与COALESCE（简洁高效） #

如果你的数据库支持GREATEST函数（如MySQL、PostgreSQL、Oracle），可以用以下写法：

DELETE FROM Mytable
WHERE GREATEST(
    COALESCE(col2, 0),
    COALESCE(col3, 0),
    COALESCE(col4, 0),
    COALESCE(col5, 0),
    COALESCE(col6, 0)
) = 0;

原理：COALESCE将NULL值转换为0，GREATEST取所有指定列的最大值。如果最大值为0，说明所有列的值都是0或NULL，符合删除条件。

如果数据库支持LEAST且列不会出现负数，也可以用LEAST替代GREATEST，效果一致。

方法2：用聚合判断存在有效数据 #

通过子查询判断当前行是否有非0非NULL的有效数据，没有则删除：

DELETE FROM Mytable t_main
WHERE NOT EXISTS (
    SELECT 1
    FROM (
        SELECT 
            CASE WHEN col2 NOT IN (0, NULL) THEN 1 END AS val2,
            CASE WHEN col3 NOT IN (0, NULL) THEN 1 END AS val3,
            CASE WHEN col4 NOT IN (0, NULL) THEN 1 END AS val4,
            CASE WHEN col5 NOT IN (0, NULL) THEN 1 END AS val5,
            CASE WHEN col6 NOT IN (0, NULL) THEN 1 END AS val6
        FROM Mytable t_sub
        WHERE t_sub.主键列 = t_main.主键列 -- 替换为表的主键或唯一标识列
    ) sub
    WHERE val2 IS NOT NULL OR val3 IS NOT NULL OR val4 IS NOT NULL OR val5 IS NOT NULL OR val6 IS NOT NULL
);

原理：子查询中给每个有效列标记1，只要有一个标记存在，说明该行不符合删除条件，通过NOT EXISTS筛选出需要删除的行。

方法3：动态生成SQL（适合列数极多的场景） #

如果指定列数量很多（比如20+列），手动写列名效率低，可以利用数据库元数据自动生成SQL。以MySQL为例：

SELECT CONCAT(
    'DELETE FROM Mytable WHERE GREATEST(',
    GROUP_CONCAT('COALESCE(', COLUMN_NAME, ', 0)'),
    ') = 0;'
) AS auto_generated_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Mytable'
  AND COLUMN_NAME IN ('col2','col3','col4','col5','col6'); -- 替换为你的指定列子集

执行该查询会直接生成完整的DELETE语句，复制执行即可。PostgreSQL、SQL Server等数据库也有对应的元数据表（如INFORMATION_SCHEMA.COLUMNS）可以实现类似功能。

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