如何编写SQL查询生成按日期维度统计的学生考勤报表

需求背景 #

• 现有学生考勤原始数据
• 需要输出每名学生对应各日期的考勤情况，最终结果为学生维度行、日期维度列的宽表格式

实现方案 #

该需求属于典型的SQL行转列（透视）场景，根据使用的数据库类型、日期是否固定，可选择对应写法：

1. 通用静态写法（全数据库兼容，需提前枚举所有统计日期） #

如果需要统计的日期范围是固定的，用CASE WHEN+聚合函数的写法可以在所有支持SQL的数据库中运行，无语法兼容问题：

SELECT
  student_id,
  student_name,
  MAX(CASE WHEN attend_date = '2024-01-01' THEN attend_status END) AS `2024-01-01`,
  MAX(CASE WHEN attend_date = '2024-01-02' THEN attend_status END) AS `2024-01-02`,
  MAX(CASE WHEN attend_date = '2024-01-03' THEN attend_status END) AS `2024-01-03`
  -- 按实际需要统计的日期，依次补充对应CASE WHEN语句即可
FROM attendance_raw -- 替换为实际的考勤原始表名
GROUP BY student_id, student_name
ORDER BY student_id;

说明：由于同一个学生同一天只会存在一条考勤记录，用MAX/MIN聚合都可以正常取到唯一的考勤状态，不会出现数据冲突。

2. 动态透视写法（无需手动枚举日期，自动适配日期范围） #

如果统计日期不固定，不想每次修改SQL枚举日期，可以用对应数据库的动态语法实现：

MySQL 动态实现

通过拼接动态SQL自动生成所有日期列：

SET @sql = NULL;
-- 自动从源表读取所有不重复的考勤日期，拼接透视逻辑
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN attend_date = ''',
      attend_date,
      ''' THEN attend_status END) AS `',
      attend_date, '`'
    )
  ) INTO @sql
FROM attendance_raw;

-- 拼接完整查询语句并执行
SET @sql = CONCAT('SELECT student_id, student_name, ', @sql, ' 
                  FROM attendance_raw 
                  GROUP BY student_id, student_name
                  ORDER BY student_id');

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

SQL Server 实现

用原生PIVOT运算符实现透视，动态场景同样通过拼接SQL实现：

-- 静态PIVOT写法（固定日期）
SELECT student_id, student_name, [2024-01-01], [2024-01-02], [2024-01-03]
FROM attendance_raw
PIVOT(
  MAX(attend_status)
  FOR attend_date IN ([2024-01-01], [2024-01-02], [2024-01-03])
) AS pvt
ORDER BY student_id;

PostgreSQL 实现

用crosstab透视函数实现，首次使用需要先开启扩展：

-- 首次执行需要先创建tablefunc扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT student_id, student_name, attend_date, attend_status
   FROM attendance_raw
   ORDER BY 1,2,3',
  'SELECT DISTINCT attend_date FROM attendance_raw ORDER BY 1'
) AS final_result(
  student_id INT,
  student_name VARCHAR(50),
  "2024-01-01" VARCHAR(20),
  "2024-01-02" VARCHAR(20),
  "2024-01-03" VARCHAR(20)
  -- 静态场景下补充所有日期字段即可
);

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