如何动态过滤数据库表中所有字段均为0的行？

我有一张包含20余个字段的数据库表，部分行的所有字段值均为0。需要过滤掉这些全0行，但不想手动编写所有字段名（且未来可能新增字段）。

我曾尝试两种方法，但都有局限：

• 方法一：

select * 
from table 
where concat(col1, col2, col3) != '000'

但字段数量多且可能新增，手动写20余个0太繁琐；

• 方法二：

select * 
from table 
where coalesce(col1, col2, col3) > 0 
   or coalesce(col1, col2, col3) < 0 

考虑到部分值可能为负数，但同样不想手动编写20余个字段名。

有没有动态解决该问题的方法？

针对不同数据库的动态解决方案 #

1. MySQL/MariaDB #

利用系统表information_schema.columns自动获取目标表的字段名，动态拼接过滤条件：

SET @table_name = 'your_table_name'; -- 替换为你的表名
SET @where_clause = '';

SELECT GROUP_CONCAT(
    CONCAT(`column_name`, ' != 0') SEPARATOR ' OR '
) INTO @where_clause
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = @table_name;

SET @sql = CONCAT('SELECT * FROM ', @table_name, ' WHERE ', @where_clause);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

脚本会自动生成col1 !=0 OR col2 !=0 OR ...的条件，只要有一个字段不为0就保留该行，彻底避免手动维护字段列表。

2. PostgreSQL #

结合information_schema.columns与PL/pgSQL实现动态过滤：

DO $$
DECLARE
    table_name text := 'your_table_name'; -- 替换为你的表名
    where_clause text;
BEGIN
    SELECT string_agg(quote_ident(column_name) || ' != 0', ' OR ')
    INTO where_clause
    FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name = table_name; -- 若表在其他schema，修改此处

    EXECUTE format('SELECT * FROM %I WHERE %s', table_name, where_clause);
END $$;

如果需要将结果持久化，可调整EXECUTE语句将数据插入临时表。

3. SQL Server #

通过sys.columns系统视图生成动态SQL：

DECLARE @table_name NVARCHAR(128) = 'your_table_name'; -- 替换为你的表名
DECLARE @where_clause NVARCHAR(MAX);

SELECT @where_clause = STRING_AGG(QUOTENAME(name) + ' != 0', ' OR ')
FROM sys.columns
WHERE object_id = OBJECT_ID(@table_name); -- 表在指定schema时，用OBJECT_ID('schema_name.table_name')

DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM ' + @table_name + ' WHERE ' + @where_clause;

EXEC sp_executesql @sql;

注意事项 #

• 若表中存在非数值类型字段，需在查询系统表时添加过滤条件，比如AND data_type IN ('int', 'bigint', 'decimal', 'float')；
• 如果字段允许NULL，需将条件调整为(col IS NOT NULL AND col !=0)，避免NULL被误判为非0值；
• 动态SQL需注意安全问题，使用quote_ident（PostgreSQL）或QUOTENAME（SQL Server）处理字段名，避免SQL注入风险。

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