如何用MySQL实现员工考勤数据横向循环Join并按月展示出勤情况

我刚好处理过类似的考勤报表需求，这里给你两种可行的实现方案，分别对应固定日期范围和指定完整月份的场景：

一、静态日期范围的JOIN查询语句 #

如果你的日期范围是固定的（比如示例中的2018-05-01至04），可以直接用左连接+CASE表达式来实现横向展示。假设你的员工表名为employees，出勤记录表名为attendance，SQL语句如下：

SELECT 
    e.name,
    MAX(CASE WHEN a.date = '2018-05-01' THEN 1 ELSE NULL END) AS `2018-05-01`,
    MAX(CASE WHEN a.date = '2018-05-02' THEN 1 ELSE NULL END) AS `2018-05-02`,
    MAX(CASE WHEN a.date = '2018-05-03' THEN 1 ELSE NULL END) AS `2018-05-03`,
    MAX(CASE WHEN a.date = '2018-05-04' THEN 1 ELSE NULL END) AS `2018-05-04`
FROM employees e
LEFT JOIN attendance a ON e.ids = a.ids
GROUP BY e.ids, e.name
ORDER BY e.ids;

逻辑说明： #

1. 用employees作为主表左连接attendance，确保所有员工都能被展示，哪怕没有出勤记录。
2. 每个日期对应一个CASE表达式：如果该员工当天有出勤记录，返回1，否则返回NULL（空值）。
3. 用MAX()聚合函数是因为左连接后，每个员工每天最多一条记录，分组后能保留存在的1，缺失的则显示NULL，符合你要的“空表示缺勤”的需求。
4. 按e.ids分组是为了避免同名员工被合并，保证数据准确性。

二、指定完整月份的动态迭代方案 #

如果需要展示任意指定月份的所有日期，静态写CASE显然不现实，这时候可以用动态生成SQL的方式实现，分两种场景：

1. MySQL存储过程实现（数据库端处理） #

通过存储过程遍历指定月份的所有日期，自动拼接SQL语句并执行：

DELIMITER //
CREATE PROCEDURE get_attendance_monthly_report(IN target_month VARCHAR(7))
BEGIN
    DECLARE dynamic_sql VARCHAR(4000);
    DECLARE date_str VARCHAR(10);
    DECLARE done INT DEFAULT FALSE;
    
    -- 游标：获取指定月份的所有日期
    DECLARE date_cursor CURSOR FOR
        SELECT DATE_FORMAT(date, '%Y-%m-%d') AS date_str
        FROM (
            SELECT DATE_ADD(CONCAT(target_month, '-01'), INTERVAL (seq - 1) DAY) AS date
            FROM (
                SELECT 1 + seq.seq AS seq
                FROM (
                    SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
                    SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
                    SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
                    SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL
                    SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL
                    SELECT 30 UNION ALL SELECT 31
                ) seq
            ) seq_dates
            WHERE DATE_FORMAT(date, '%Y-%m') = target_month
        ) month_dates;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 初始化SQL头部
    SET dynamic_sql = 'SELECT e.name';

    -- 遍历日期，拼接每个日期的CASE子句
    OPEN date_cursor;
    read_loop: LOOP
        FETCH date_cursor INTO date_str;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET dynamic_sql = CONCAT(dynamic_sql, ', MAX(CASE WHEN a.date = ''', date_str, ''' THEN 1 ELSE NULL END) AS `', date_str, '`');
    END LOOP;
    CLOSE date_cursor;

    -- 拼接SQL尾部并执行
    SET dynamic_sql = CONCAT(
        dynamic_sql,
        ' FROM employees e LEFT JOIN attendance a ON e.ids = a.ids ',
        ' WHERE DATE_FORMAT(a.date, ''%Y-%m'') = ''', target_month, ''' OR a.date IS NULL ',
        ' GROUP BY e.ids, e.name ORDER BY e.ids'
    );

    PREPARE stmt FROM dynamic_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

使用方式：

调用存储过程传入目标月份（格式YYYY-MM）即可生成报表：

CALL get_attendance_monthly_report('2018-05');

2. 应用层迭代实现（业务代码端处理） #

如果不想在数据库创建存储过程，也可以在应用层（比如Python、PHP）生成日期列表，再拼接SQL语句。以Python为例：

import mysql.connector
from datetime import datetime, timedelta

def generate_monthly_attendance(month_str):
    # 解析月份，获取当月首尾日期
    year, month = map(int, month_str.split('-'))
    first_day = datetime(year, month, 1)
    last_day = datetime(year, month+1, 1) - timedelta(days=1) if month !=12 else datetime(year+1,1,1)-timedelta(days=1)
    
    # 生成当月所有日期字符串
    date_list = []
    current = first_day
    while current <= last_day:
        date_list.append(current.strftime('%Y-%m-%d'))
        current += timedelta(days=1)
    
    # 拼接CASE子句
    case_clauses = [f"MAX(CASE WHEN a.date = '{d}' THEN 1 ELSE NULL END) AS `{d}`" for d in date_list]
    
    # 拼接完整SQL
    sql = f"""
        SELECT e.name, {', '.join(case_clauses)}
        FROM employees e
        LEFT JOIN attendance a ON e.ids = a.ids
        GROUP BY e.ids, e.name
        ORDER BY e.ids
    """
    
    # 执行查询（请替换为你的数据库连接信息）
    conn = mysql.connector.connect(
        host='your_host',
        user='your_username',
        password='your_password',
        database='your_database'
    )
    cursor = conn.cursor()
    cursor.execute(sql)
    
    # 打印表头和结果
    print([col[0] for col in cursor.description])
    for row in cursor.fetchall():
        print(row)
    
    cursor.close()
    conn.close()

# 生成2018年5月的出勤报表
generate_monthly_attendance('2018-05')

注意事项： #

• 确保attendance表的date字段是DATE类型，避免日期格式错误导致匹配失败。
• 如果员工当月无任何出勤记录，所有日期列都会显示为空，符合需求。
• 分组时必须使用员工唯一ID（e.ids），防止同名员工数据被合并。

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