如何从MySQL数据库提取员工入职首月的工作天数？

一、计算入职首月的自然天数 #

如果要统计从入职当天到当月月底的自然天数，用LAST_DAY()配合DATEDIFF()比TIMESTAMPDIFF()更直接：

SELECT 
    first_name,
    last_name,
    hire_date,
    -- 月底日期减入职日期再加1，确保包含入职当天
    DATEDIFF(LAST_DAY(hire_date), hire_date) + 1 AS first_month_natural_days
FROM employees;

• LAST_DAY(hire_date)：返回入职日期所在月份的最后一天（比如2024-03-12的结果是2024-03-31）
• DATEDIFF(end, start)：计算两个日期的天数差，加1是因为要把入职当天算进去

二、计算入职首月的工作日天数（排除周末） #

如果需要统计周一到周五的工作日数量，有两种实现方式：

方法1：生成日期序列逐天判断（适合小数据量） #

SELECT 
    first_name,
    last_name,
    hire_date,
    SUM(
        CASE 
            WHEN WEEKDAY(day) BETWEEN 0 AND 4 THEN 1 -- 0代表周一，4代表周五
            ELSE 0 
        END
    ) AS first_month_work_days
FROM (
    -- 生成入职日到当月月底的所有日期
    SELECT hire_date + INTERVAL n DAY AS day
    FROM employees
    CROSS JOIN (
        SELECT 0 AS n 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
    ) AS numbers
    WHERE hire_date + INTERVAL n DAY <= LAST_DAY(hire_date)
) AS date_range
GROUP BY first_name, last_name, hire_date;

方法2：公式计算（高效，无需生成日期） #

通过规律计算周末天数，再用总天数减去周末天数得到工作日：

SELECT 
    first_name,
    last_name,
    hire_date,
    @total_days := DATEDIFF(LAST_DAY(hire_date), hire_date) + 1 AS total_days,
    @weekend_days := 
        FLOOR(@total_days / 7) * 2 +
        CASE WHEN WEEKDAY(hire_date) > WEEKDAY(LAST_DAY(hire_date)) THEN 2 ELSE 0 END +
        CASE WHEN WEEKDAY(hire_date) = 5 THEN 1 ELSE 0 END +
        CASE WHEN WEEKDAY(LAST_DAY(hire_date)) = 6 THEN 1 ELSE 0 END,
    @total_days - @weekend_days AS first_month_work_days
FROM employees;

关于TIMESTAMPDIFF()的补充 #

你提到的TIMESTAMPDIFF()语法是TIMESTAMPDIFF(unit, start_date, end_date)，返回两个日期的差值，单位支持DAY/MONTH/YEAR等。比如计算入职到当前的天数：

SELECT TIMESTAMPDIFF(DAY, hire_date, CURDATE()) AS days_since_hire FROM employees;

但计算首月天数的场景下，LAST_DAY()+DATEDIFF()的组合更简洁。

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