基于员工startdate按月统计工时的SQL迭代查询需求

需求拆解 #

• 基于Table1的员工入职日期，生成从入职月到当前月的月度区间：每个区间是「当月入职日」到「下月入职日的前一天」（比如入职日2022-02-15，第一个区间就是2022-02-15至2022-03-14）
• 关联Table2的考勤数据，统计每个员工在对应区间内的总工时，无考勤数据时返回NULL

实现步骤 #

1. 生成员工月度区间表 #

用递归CTE（Common Table Expression）来生成每位员工的所有月度区间，主流数据库（SQL Server、MySQL 8.0+、PostgreSQL等）都支持这种写法。

SQL Server 版本

WITH EmployeeMonths AS (
    -- 初始行：员工入职第一个月的区间
    SELECT 
        emp AS Emp#,
        startdate AS startdate1,
        DATEADD(DAY, -1, DATEADD(MONTH, 1, startdate)) AS enddate,
        -- 用年月数值标记，方便终止递归
        DATEPART(YEAR, startdate) * 12 + DATEPART(MONTH, startdate) AS month_num
    FROM Table1
    UNION ALL
    -- 递归生成后续每个月的区间
    SELECT 
        Emp#,
        DATEADD(MONTH, 1, startdate1) AS startdate1,
        DATEADD(DAY, -1, DATEADD(MONTH, 2, startdate1)) AS enddate,
        month_num + 1
    FROM EmployeeMonths
    -- 终止条件：不超过当前年月
    WHERE month_num < DATEPART(YEAR, GETDATE()) * 12 + DATEPART(MONTH, GETDATE())
)
SELECT * FROM EmployeeMonths ORDER BY Emp#, startdate1;

MySQL 8.0+ 版本

WITH RECURSIVE EmployeeMonths AS (
    SELECT 
        emp AS Emp#,
        startdate AS startdate1,
        DATE_SUB(DATE_ADD(startdate, INTERVAL 1 MONTH), INTERVAL 1 DAY) AS enddate,
        YEAR(startdate)*12 + MONTH(startdate) AS month_num
    FROM Table1
    UNION ALL
    SELECT 
        Emp#,
        DATE_ADD(startdate1, INTERVAL 1 MONTH) AS startdate1,
        DATE_SUB(DATE_ADD(startdate1, INTERVAL 2 MONTH), INTERVAL 1 DAY) AS enddate,
        month_num + 1
    FROM EmployeeMonths
    WHERE month_num < YEAR(CURDATE())*12 + MONTH(CURDATE())
)
SELECT * FROM EmployeeMonths ORDER BY Emp#, startdate1;

2. 关联考勤表统计总工时 #

把上面生成的区间表和Table2做左连接，分组统计每个区间的总工时：

SQL Server 完整查询

WITH EmployeeMonths AS (
    SELECT 
        emp AS Emp#,
        startdate AS startdate1,
        DATEADD(DAY, -1, DATEADD(MONTH, 1, startdate)) AS enddate,
        DATEPART(YEAR, startdate) * 12 + DATEPART(MONTH, startdate) AS month_num
    FROM Table1
    UNION ALL
    SELECT 
        Emp#,
        DATEADD(MONTH, 1, startdate1) AS startdate1,
        DATEADD(DAY, -1, DATEADD(MONTH, 2, startdate1)) AS enddate,
        month_num + 1
    FROM EmployeeMonths
    WHERE month_num < DATEPART(YEAR, GETDATE()) * 12 + DATEPART(MONTH, GETDATE())
)
SELECT 
    em.Emp#,
    em.startdate1,
    em.enddate,
    SUM(t2.hrs) AS total_hrs
FROM EmployeeMonths em
LEFT JOIN Table2 t2 
    ON em.Emp# = t2.Emp#
    AND t2.[attend date] BETWEEN em.startdate1 AND em.enddate
GROUP BY em.Emp#, em.startdate1, em.enddate
ORDER BY em.Emp#, em.startdate1;

MySQL 完整查询

WITH RECURSIVE EmployeeMonths AS (
    SELECT 
        emp AS Emp#,
        startdate AS startdate1,
        DATE_SUB(DATE_ADD(startdate, INTERVAL 1 MONTH), INTERVAL 1 DAY) AS enddate,
        YEAR(startdate)*12 + MONTH(startdate) AS month_num
    FROM Table1
    UNION ALL
    SELECT 
        Emp#,
        DATE_ADD(startdate1, INTERVAL 1 MONTH) AS startdate1,
        DATE_SUB(DATE_ADD(startdate1, INTERVAL 2 MONTH), INTERVAL 1 DAY) AS enddate,
        month_num + 1
    FROM EmployeeMonths
    WHERE month_num < YEAR(CURDATE())*12 + MONTH(CURDATE())
)
SELECT 
    em.Emp#,
    em.startdate1,
    em.enddate,
    SUM(t2.hrs) AS total_hrs
FROM EmployeeMonths em
LEFT JOIN Table2 t2 
    ON em.Emp# = t2.Emp#
    AND t2.`attend date` BETWEEN em.startdate1 AND em.enddate
GROUP BY em.Emp#, em.startdate1, em.enddate
ORDER BY em.Emp#, em.startdate1;

关键说明 #

• 递归CTE会自动遍历每个员工的入职月到当前月，生成符合要求的月度区间，不用手动写迭代逻辑
• 左连接保证了即使某个区间没有考勤数据，也会保留该区间的记录，SUM(t2.hrs)会返回NULL
• 不同数据库的日期函数略有差异，上面分别适配了SQL Server和MySQL的写法，其他数据库（比如PostgreSQL）只需要调整日期函数即可

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