MySQL按月计算指定日期范围员工平均薪资方案（兼容5.6/8+）

问题背景 #

我们有一个employees表，记录了员工的入职、离职日期和薪资。需要针对给定的日期范围，按月统计每个月在职员工的平均薪资，且要求纯MySQL实现，同时兼容MySQL 5.6和MySQL 8+版本，避免循环查询每个月的低效方式。

首先确认表结构和测试数据：

CREATE TABLE `employees` (
 `id` int(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 `start_date` date NOT NULL,
 `end_date` date NOT NULL,
 `salary` decimal(10,2) DEFAULT NULL
);

INSERT INTO `employees` (`id`, `name`, `start_date`, `end_date`, `salary`) VALUES
(1, 'Mark', '2017-05-01', '2020-01-31', '2000.00'),
(2, 'Tania', '2018-02-01', '2019-08-31', '5000.00'),
(3, 'Leo', '2018-02-01', '2018-09-30', '3000.00'),
(4, 'Elsa', '2018-12-01', '2020-05-31', '4000.00');

核心思路 #

• 生成目标日期范围内的所有月份：这是实现高效统计的关键，我们需要先构建出统计周期内的每个月份，而非依赖现有数据中的月份记录。
• 匹配每个月份的在职员工：判断员工的在职周期与当前月份是否有重叠（即员工的start_date ≤ 当月最后一天，且end_date ≥ 当月第一天）。
• 计算月度平均薪资：对每个月份的在职员工薪资求平均值，保留两位小数以保证结果精度。

MySQL 8+ 版本方案 #

MySQL 8+支持递归CTE（公共表表达式），可以轻松生成连续的月份序列，代码简洁易维护：

WITH RECURSIVE date_range AS (
    -- 初始化起始月份：将输入的开始日期转为当月第一天
    SELECT 
        DATE_FORMAT('2018-08-01', '%Y-%m-01') AS month_start,
        LAST_DAY('2018-08-01') AS month_end
    UNION ALL
    -- 递归生成后续月份，直到超过目标结束日期
    SELECT 
        DATE_ADD(month_start, INTERVAL 1 MONTH),
        LAST_DAY(DATE_ADD(month_start, INTERVAL 1 MONTH))
    FROM date_range
    WHERE month_start < DATE_FORMAT('2019-01-31', '%Y-%m-01')
)
SELECT
    YEAR(dr.month_start) AS `year`,
    LPAD(MONTH(dr.month_start), 2, '0') AS `month`,
    ROUND(AVG(e.salary), 2) AS avg_salary
FROM date_range dr
LEFT JOIN employees e 
    ON e.start_date <= dr.month_end 
    AND e.end_date >= dr.month_start
GROUP BY dr.month_start
ORDER BY dr.month_start;

关键说明 #

• 递归CTE date_range会自动生成指定范围内的所有月份的起始和结束日期。
• JOIN条件确保只统计在当前月份内有在职记录的员工（哪怕只在职一天也算）。
• LPAD函数保证月份显示为两位数字（如08而非8），与预期结果格式一致。

MySQL 5.6 版本方案 #

MySQL 5.6不支持CTE，我们可以通过数字辅助表来生成连续月份序列，有两种实现方式：

方法1：临时数字表方案 #

-- 临时创建一个包含连续数字的表（这里生成0到100的数字，足够覆盖大部分场景）
CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
INSERT INTO nums SELECT n+11 FROM nums; -- 重复执行可扩展数字范围

-- 生成目标月份并统计平均薪资
SELECT
    YEAR(month_start) AS `year`,
    LPAD(MONTH(month_start), 2, '0') AS `month`,
    ROUND(AVG(e.salary), 2) AS avg_salary
FROM (
    SELECT
        DATE_ADD(
            DATE_FORMAT('2018-08-01', '%Y-%m-01'),
            INTERVAL n MONTH
        ) AS month_start,
        LAST_DAY(
            DATE_ADD(
                DATE_FORMAT('2018-08-01', '%Y-%m-01'),
                INTERVAL n MONTH
            )
        ) AS month_end
    FROM nums
    WHERE DATE_ADD(
            DATE_FORMAT('2018-08-01', '%Y-%m-01'),
            INTERVAL n MONTH
        ) <= DATE_FORMAT('2019-01-31', '%Y-%m-01')
) dr
LEFT JOIN employees e 
    ON e.start_date <= dr.month_end 
    AND e.end_date >= dr.month_start
GROUP BY dr.month_start
ORDER BY dr.month_start;

-- 可选：统计完成后删除临时表
DROP TEMPORARY TABLE nums;

方法2：无临时表方案（利用系统表生成数字） #

如果不想创建临时表，可以借助系统表生成连续数字：

SELECT
    YEAR(month_start) AS `year`,
    LPAD(MONTH(month_start), 2, '0') AS `month`,
    ROUND(AVG(e.salary), 2) AS avg_salary
FROM (
    SELECT
        DATE_ADD(
            DATE_FORMAT('2018-08-01', '%Y-%m-01'),
            INTERVAL (a.n + b.n * 10) MONTH
        ) AS month_start,
        LAST_DAY(
            DATE_ADD(
                DATE_FORMAT('2018-08-01', '%Y-%m-01'),
                INTERVAL (a.n + b.n * 10) MONTH
            )
        ) AS month_end
    FROM (
        SELECT 0 AS n 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
    ) a
    CROSS JOIN (
        SELECT 0 AS n 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
    ) b
    WHERE DATE_ADD(
            DATE_FORMAT('2018-08-01', '%Y-%m-01'),
            INTERVAL (a.n + b.n * 10) MONTH
        ) <= DATE_FORMAT('2019-01-31', '%Y-%m-01')
) dr
LEFT JOIN employees e 
    ON e.start_date <= dr.month_end 
    AND e.end_date >= dr.month_start
GROUP BY dr.month_start
ORDER BY dr.month_start;

测试验证 #

当输入日期范围为2018-08-01至2019-01-31时，两种方案都会返回预期结果：

+------+-------+------------+
| year | month | avg_salary |
+------+-------+------------+
| 2018 | 08    | 3333.33    |
| 2018 | 09    | 3333.33    |
| 2018 | 10    | 3500.00    |
| 2018 | 11    | 3500.00    |
| 2018 | 12    | 3666.67    |
| 2019 | 01    | 3666.67    |
+------+-------+------------+

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