优化MySQL考勤统计查询：宽表转考勤类型日期聚合结果

原查询通过硬编码日期列实现考勤统计，扩展到31天会导致代码冗余、维护成本高。推荐采用宽表转窄表的思路优化，利用MySQL的递归CTE和聚合函数实现灵活统计：

核心思路 #

1. 将原表中01至31的日期列转置为行数据，每行对应员工的单日考勤记录
2. 按员工+考勤类型分组，聚合生成对应日期列表
3. 按需将聚合结果转回宽表格式，匹配需求输出字段

完整优化查询（MySQL 8.0+） #

WITH date_list AS (
    -- 生成01到31的日期字符串列表
    SELECT LPAD(n, 2, '0') AS day_str
    FROM (
        SELECT 1 + t1.i + t2.i*10 AS n
        FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
             (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t2
        HAVING n <= 31
    ) nums
),
unpivoted_attendance AS (
    -- 将宽表转置为窄表：员工ID、姓名、月份、日期、考勤状态
    SELECT 
        a.empid AS emp_id,
        a.name AS emp_name,
        a.month,
        dl.day_str AS day,
        -- 动态匹配对应日期列的考勤值
        CASE dl.day_str
            WHEN '01' THEN a.`01` WHEN '02' THEN a.`02` WHEN '03' THEN a.`03` WHEN '04' THEN a.`04`
            WHEN '05' THEN a.`05` WHEN '06' THEN a.`06` WHEN '07' THEN a.`07` WHEN '08' THEN a.`08`
            WHEN '09' THEN a.`09` WHEN '10' THEN a.`10` WHEN '11' THEN a.`11` WHEN '12' THEN a.`12`
            WHEN '13' THEN a.`13` WHEN '14' THEN a.`14` WHEN '15' THEN a.`15` WHEN '16' THEN a.`16`
            WHEN '17' THEN a.`17` WHEN '18' THEN a.`18` WHEN '19' THEN a.`19` WHEN '20' THEN a.`20`
            WHEN '21' THEN a.`21` WHEN '22' THEN a.`22` WHEN '23' THEN a.`23` WHEN '24' THEN a.`24`
            WHEN '25' THEN a.`25` WHEN '26' THEN a.`26` WHEN '27' THEN a.`27` WHEN '28' THEN a.`28`
            WHEN '29' THEN a.`29` WHEN '30' THEN a.`30` WHEN '31' THEN a.`31`
        END AS attendance_status
    FROM acc_testing.attendance a
    CROSS JOIN date_list dl
    -- 过滤无效日期（如小月的31日、2月的30日）
    WHERE (a.month IN (1,3,5,7,8,10,12) OR dl.day_str <= '30')
      AND (a.month != 2 OR dl.day_str <= '29')
)
-- 按员工分组，聚合各考勤类型的日期列表
SELECT 
    emp_id,
    emp_name,
    month,
    GROUP_CONCAT(CASE WHEN attendance_status = 'P' THEN day END ORDER BY day SEPARATOR ', ') AS P_days,
    GROUP_CONCAT(CASE WHEN attendance_status = 'SL' THEN day END ORDER BY day SEPARATOR ', ') AS SL_days,
    GROUP_CONCAT(CASE WHEN attendance_status = 'LOP' THEN day END ORDER BY day SEPARATOR ', ') AS LOP_days,
    GROUP_CONCAT(CASE WHEN attendance_status = '?P+?LOP' THEN day END ORDER BY day SEPARATOR ', ') AS `?P+?LOP_days`,
    GROUP_CONCAT(CASE WHEN attendance_status = '?LOP+?P' THEN day END ORDER BY day SEPARATOR ', ') AS `?LOP+?P_days`,
    GROUP_CONCAT(CASE WHEN attendance_status = '?SL+?P' THEN day END ORDER BY day SEPARATOR ', ') AS `?SL+?P_days`
FROM unpivoted_attendance
GROUP BY emp_id, emp_name, month;

方案优势 #

• 避免硬编码大量日期列，新增考勤类型仅需在聚合步骤添加对应分支
• 逻辑清晰，易于维护和扩展
• 自动过滤无效日期，避免统计空值

低版本MySQL兼容方案（无CTE） #

如果使用MySQL 5.x版本，可通过临时表替代CTE：

-- 创建临时日期列表表
CREATE TEMPORARY TABLE date_list (day_str VARCHAR(2));
INSERT INTO date_list VALUES
('01'),('02'),('03'),('04'),('05'),('06'),('07'),('08'),('09'),('10'),
('11'),('12'),('13'),('14'),('15'),('16'),('17'),('18'),('19'),('20'),
('21'),('22'),('23'),('24'),('25'),('26'),('27'),('28'),('29'),('30'),('31');

-- 转置并聚合统计
SELECT 
    a.empid AS emp_id,
    a.name AS emp_name,
    a.month,
    GROUP_CONCAT(
        CASE dl.day_str 
            WHEN '01' THEN IF(a.`01`='P','01',NULL) WHEN '02' THEN IF(a.`02`='P','02',NULL)
            WHEN '03' THEN IF(a.`03`='P','03',NULL) WHEN '04' THEN IF(a.`04`='P','04',NULL)
            WHEN '05' THEN IF(a.`05`='P','05',NULL) WHEN '06' THEN IF(a.`06`='P','06',NULL)
            WHEN '07' THEN IF(a.`07`='P','07',NULL) WHEN '08' THEN IF(a.`08`='P','08',NULL)
            WHEN '09' THEN IF(a.`09`='P','09',NULL) WHEN '10' THEN IF(a.`10`='P','10',NULL)
            WHEN '11' THEN IF(a.`11`='P','11',NULL) WHEN '12' THEN IF(a.`12`='P','12',NULL)
            WHEN '13' THEN IF(a.`13`='P','13',NULL) WHEN '14' THEN IF(a.`14`='P','14',NULL)
            WHEN '15' THEN IF(a.`15`='P','15',NULL) WHEN '16' THEN IF(a.`16`='P','16',NULL)
            WHEN '17' THEN IF(a.`17`='P','17',NULL) WHEN '18' THEN IF(a.`18`='P','18',NULL)
            WHEN '19' THEN IF(a.`19`='P','19',NULL) WHEN '20' THEN IF(a.`20`='P','20',NULL)
            WHEN '21' THEN IF(a.`21`='P','21',NULL) WHEN '22' THEN IF(a.`22`='P','22',NULL)
            WHEN '23' THEN IF(a.`23`='P','23',NULL) WHEN '24' THEN IF(a.`24`='P','24',NULL)
            WHEN '25' THEN IF(a.`25`='P','25',NULL) WHEN '26' THEN IF(a.`26`='P','26',NULL)
            WHEN '27' THEN IF(a.`27`='P','27',NULL) WHEN '28' THEN IF(a.`28`='P','28',NULL)
            WHEN '29' THEN IF(a.`29`='P','29',NULL) WHEN '30' THEN IF(a.`30`='P','30',NULL)
            WHEN '31' THEN IF(a.`31`='P','31',NULL)
        END ORDER BY dl.day_str SEPARATOR ', '
    ) AS P_days,
    -- 其他考勤类型按上述格式替换条件即可
    GROUP_CONCAT(
        CASE dl.day_str 
            WHEN '01' THEN IF(a.`01`='SL','01',NULL) WHEN '02' THEN IF(a.`02`='SL','02',NULL)
            -- ... 剩余日期分支省略
        END ORDER BY dl.day_str SEPARATOR ', '
    ) AS SL_days
FROM acc_testing.attendance a
CROSS JOIN date_list dl
WHERE (a.month IN (1,3,5,7,8,10,12) OR dl.day_str <= '30')
  AND (a.month != 2 OR dl.day_str <= '29')
GROUP BY a.empid, a.name, a.month;

-- 清理临时表
DROP TEMPORARY TABLE date_list;

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