如何从多列提取多个0值并返回为单行结果？

问题背景 #

现有示例数据表：

Name   Apple Banana Cherry Lemon Lime
Alice   0     3      1      0     1 
John    1     0      0      2     0 

需要提取指定行中所有值为0的列，比如针对Alice行得到包含Name、Apple、Lemon的结果，针对John行得到包含Name、Banana、Cherry、Lime的结果。实际数据库有近100列，不能手动枚举列名，需要通用解决方案。

解决方案 #

核心思路是动态生成SQL，依靠数据库元数据自动识别目标行的0值列，避免硬编码列名。以下以MySQL为例给出具体实现，其他数据库可参照逻辑适配。

1. 自动筛选目标行的0值列 #

执行以下SQL，替换your_table为实际表名，'Alice'为要查询的目标行名称：

SELECT GROUP_CONCAT(column_name SEPARATOR ', ') AS zero_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = DATABASE()
  AND table_name = 'your_table'
  AND column_name != 'Name'
  AND column_type IN ('int', 'tinyint', 'smallint', 'bigint', 'float', 'double')
  AND EXISTS (
    SELECT 1 FROM your_table
    WHERE Name = 'Alice' AND your_table.`column_name` = 0
  );

这条语句会返回目标行中所有值为0的列名，比如Alice对应的结果是Apple, Lemon。

2. 自动生成并执行查询语句 #

如果想一步到位直接获取结果，可以用存储过程封装整个逻辑：

DELIMITER //
CREATE PROCEDURE GetZeroColumnsForRow(IN target_name VARCHAR(255))
BEGIN
  DECLARE zero_cols VARCHAR(1000);
  -- 获取目标行的0值列名
  SELECT GROUP_CONCAT(column_name SEPARATOR ', ') INTO zero_cols
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = DATABASE()
    AND table_name = 'your_table'
    AND column_name != 'Name'
    AND column_type IN ('int', 'tinyint', 'smallint', 'bigint', 'float', 'double')
    AND EXISTS (
      SELECT 1 FROM your_table
      WHERE Name = target_name AND your_table.`column_name` = 0
    );
  -- 拼接并执行查询SQL
  SET @sql = CONCAT('SELECT Name, ', zero_cols, ' FROM your_table WHERE Name = ''', target_name, ''';');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

调用存储过程就能直接拿到目标结果：

-- 获取Alice行的0值列数据
CALL GetZeroColumnsForRow('Alice');
-- 获取John行的0值列数据
CALL GetZeroColumnsForRow('John');

其他数据库适配提示 #

• PostgreSQL：用information_schema.columns获取元数据，动态SQL用EXECUTE语句实现
• SQL Server：元数据同样用INFORMATION_SCHEMA.COLUMNS，动态SQL通过EXEC sp_executesql执行
• 所有数据库的核心逻辑一致：通过系统元数据拿到列名，筛选目标行中值为0的列，动态拼接查询语句

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