如何用单条SQL查询指定月份范围的逐月累计入职员工数

核心思路 #

通过生成指定范围的月初日期序列，关联员工表统计每个时间点的累计符合条件员工数，避免重复编写多条查询。

SQL语句示例（以SQL Server为例） #

WITH TargetMonths AS (
    -- 定义起始月份
    SELECT DATEFROMPARTS(2021, 1, 1) AS MonthStart
    UNION ALL
    -- 递归生成后续月份，直到目标结束月份
    SELECT DATEADD(MONTH, 1, MonthStart)
    FROM TargetMonths
    WHERE MonthStart < DATEFROMPARTS(2021, 5, 1)
)
SELECT 
    tm.MonthStart,
    COUNT(ed.EmpId) AS CumulativeEmployeeCount
FROM TargetMonths tm
-- 左连接确保即使某个月初没有符合条件的员工也会输出0
LEFT JOIN STAREdee221f287.EmployeeDetail ed
    ON ed.EmpStatus = 1
    AND ed.CompanyId = 1
    AND ed.DateOfJoining < tm.MonthStart
GROUP BY tm.MonthStart
ORDER BY tm.MonthStart;

关键说明 #

1. TargetMonths CTE：通过递归方式快速生成需要的月份范围（示例中为2021年1月至5月的月初），修改DATEFROMPARTS中的年份和月份即可调整统计范围。
2. 关联与统计：左连接员工表后，通过筛选EmpStatus=1、CompanyId=1且入职时间早于当月初的记录，统计每个月初的累计员工数。
3. 兼容MySQL（8.0+版本）：将日期函数替换为MySQL语法即可：

WITH RECURSIVE TargetMonths AS (
    SELECT '2021-01-01' AS MonthStart
    UNION ALL
    SELECT DATE_ADD(MonthStart, INTERVAL 1 MONTH)
    FROM TargetMonths
    WHERE MonthStart < '2021-05-01'
)
SELECT 
    tm.MonthStart,
    COUNT(ed.EmpId) AS CumulativeEmployeeCount
FROM TargetMonths tm
LEFT JOIN STAREdee221f287.EmployeeDetail ed
    ON ed.EmpStatus = 1
    AND ed.CompanyId = 1
    AND ed.DateOfJoining < tm.MonthStart
GROUP BY tm.MonthStart
ORDER BY tm.MonthStart;

4. 无递归CTE的替代方案：如果数据库不支持递归CTE，可以提前创建一个日期维度表，直接从中筛选目标月份范围关联统计即可。

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