MySQL实现员工每日考勤动态计算问题求助

原SQL存在的核心问题 #

1. 日期比较语法错误：Date = ', Date, ' 中日期字符串未加单引号，数据库会把2023-01-01解析为数值运算（2023-1-1=2021），和实际日期字符串类型不匹配，触发Truncated incorrect DECIMAL value警告。
2. 关联字段错误：员工表和考勤表的关联字段是userID，但原SQL误用了不存在的eID字段，导致关联失败，结果返回NULL。
3. 时长计算逻辑错误：SUM(CASE WHEN Remarks="IN" THEN TIME END) 错误地对时间类型字段使用SUM函数，应使用MAX或MIN获取员工当日的签到/签退时间（假设每日仅一次签到签退）。
4. 列名标识错误：动态生成的日期列名使用双引号，默认MySQL模式下双引号会被视为标识符，需改用反引号包裹。

修正后的动态SQL代码 #

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      '(CASE WHEN b.Date = ''',
      Date,
      ''' THEN TIMESTAMPDIFF(HOUR, ',
      'MAX(CASE WHEN b.Remarks=''IN'' THEN b.Time END), ',
      'MAX(CASE WHEN b.Remarks=''OUT'' THEN b.Time END)',
      ') END) AS `',
      Date,
      '`'
    )
  ) INTO @sql
FROM tblattendance;

SET @sql =
  CONCAT('SELECT a.Fullname, a.`Employee ID` AS EmployeeID, ', @sql, ' ',
         'FROM tblemployee a LEFT JOIN tblattendance b ON b.userID = a.userID ',
         'GROUP BY a.userID, a.Fullname, a.`Employee ID`');

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

关键修正说明 #

• 给日期字符串添加单引号，确保字符串比较的正确性，消除类型转换警告。
• 将关联字段修正为userID，保证员工表和考勤表能正确关联。
• 使用MAX()获取当日的签到/签退时间（若存在多次签到签退，可根据需求改为MIN()取最早签到、MAX()取最晚签退）。
• 用反引号包裹动态生成的日期列名，避免标识符语法错误。
• 分组时包含所有非聚合字段，符合SQL严格分组要求。

执行结果 #

运行修正后的SQL后，将得到如下输出（注：实际时长为10小时，若需按9小时统计，可根据业务需求调整计算逻辑，例如减去休息时间）：

| Fullname      | EmployeeID | 2023-01-01 |
| John Doe      | ABC-0001   | 10         |
| Jeffrey Doe   | ABC-0002   | 10         |

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