SQL技术问询：如何统计多列中指定值(1)的数量并按列名返回

一、手动编写SQL（适合少量列） #

如果列数不多，可以直接针对每一列写统计逻辑，以你的示例表（假设表名为attendance，需排除的员工代码列名为emp_code）为例：

方式1：返回分栏结果

SELECT
    'Ben' AS 员工姓名,
    SUM(CASE WHEN Ben = 1 THEN 1 ELSE 0 END) AS 出勤次数
FROM attendance
UNION ALL
SELECT
    'Kate' AS 员工姓名,
    SUM(CASE WHEN Kate = 1 THEN 1 ELSE 0 END) AS 出勤次数
FROM attendance
UNION ALL
SELECT
    'Peter' AS 员工姓名,
    SUM(CASE WHEN Peter = 1 THEN 1 ELSE 0 END) AS 出勤次数
FROM attendance
UNION ALL
SELECT
    'Sally' AS 员工姓名,
    SUM(CASE WHEN Sally = 1 THEN 1 ELSE 0 END) AS 出勤次数
FROM attendance;

方式2：返回与你期望格式一致的字符串结果

SELECT CONCAT('Ben: ', SUM(CASE WHEN Ben = 1 THEN 1 ELSE 0 END)) AS 统计结果 FROM attendance
UNION ALL
SELECT CONCAT('Kate: ', SUM(CASE WHEN Kate = 1 THEN 1 ELSE 0 END)) FROM attendance
UNION ALL
SELECT CONCAT('Peter: ', SUM(CASE WHEN Peter = 1 THEN 1 ELSE 0 END)) FROM attendance
UNION ALL
SELECT CONCAT('Sally: ', SUM(CASE WHEN Sally = 1 THEN 1 ELSE 0 END)) FROM attendance;

二、动态生成SQL（适合300+列的场景） #

因为你有300多列，手动编写效率极低，用动态SQL可以自动生成统计语句，以下是主流数据库的实现方式：

MySQL 版本

SET @sql = NULL;

-- 自动生成各列的统计语句并拼接
SELECT GROUP_CONCAT(
    CONCAT(
        'SELECT CONCAT(''', column_name, ': '', SUM(CASE WHEN `', column_name, '` = 1 THEN 1 ELSE 0 END)) AS 统计结果 FROM attendance'
    ) SEPARATOR ' UNION ALL '
) INTO @sql
FROM information_schema.columns
WHERE table_name = 'attendance'
  AND column_name != 'emp_code'; -- 排除员工代码列

-- 执行动态生成的SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 版本

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STRING_AGG(
    CONCAT(
        'SELECT CONCAT(''', name, ': '', SUM(CASE WHEN [', name, '] = 1 THEN 1 ELSE 0 END)) AS 统计结果 FROM attendance'
    ), ' UNION ALL '
)
FROM sys.columns
WHERE object_id = OBJECT_ID('attendance')
  AND name != 'emp_code';

EXEC sp_executesql @sql;

PostgreSQL 版本

DO $$
DECLARE
    sql_text TEXT;
BEGIN
    SELECT STRING_AGG(
        CONCAT(
            'SELECT CONCAT(''', column_name, ': '', SUM(CASE WHEN "', column_name, '" = 1 THEN 1 ELSE 0 END)) AS 统计结果 FROM attendance'
        ), ' UNION ALL '
    ) INTO sql_text
    FROM information_schema.columns
    WHERE table_name = 'attendance'
      AND column_name != 'emp_code';

    EXECUTE sql_text;
END $$;

注意事项 #

• 如果列名包含空格、特殊字符或关键字，需要用对应数据库的标识符包裹：MySQL用反引号`，SQL Server用方括号[]，PostgreSQL用双引号""。
• 确保执行动态SQL的账号有查询系统表（如information_schema.columns）的权限。

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