MySQL日期字段按日月筛选、入职周年临近员工查询及扩展

你可以通过以下两种方式调整原查询，实现查询指定日期范围内即将迎来入职周年的员工，并且按周年纪念日的先后顺序排序：

方法一：基于DAYOFYEAR的简化实现（接近原查询逻辑） #

这种方法和你原有的查询思路一致，通过年中日（DAYOFYEAR）判断周年是否落在指定区间，同时处理跨年场景：

-- 设置指定的日期范围
SET @start_date = '2023-01-09';
SET @end_date = '2023-09-01';

SELECT 
    EmpID,
    Date_of_joining,
    -- 计算周年纪念日距离起始日期的天数，用于排序
    (366 + DAYOFYEAR(Date_of_joining) - DAYOFYEAR(@start_date)) % 366 AS days_from_start
FROM employees
WHERE 
    -- 分两种情况判断周年是否在指定区间内
    (
        -- 日期范围在同一年的情况
        YEAR(@start_date) = YEAR(@end_date)
        AND DAYOFYEAR(Date_of_joining) BETWEEN DAYOFYEAR(@start_date) AND DAYOFYEAR(@end_date)
    )
    OR
    (
        -- 日期范围跨年的情况（比如2023-12-20到2024-01-10）
        YEAR(@start_date) < YEAR(@end_date)
        AND (DAYOFYEAR(Date_of_joining) >= DAYOFYEAR(@start_date) OR DAYOFYEAR(Date_of_joining) <= DAYOFYEAR(@end_date))
    )
ORDER BY days_from_start ASC;

逻辑说明： #

1. 用DAYOFYEAR提取日期的年中日数值，避免处理月日字符串的繁琐操作。
2. (366 + DAYOFYEAR(Date_of_joining) - DAYOFYEAR(@start_date)) % 366计算入职周年纪念日距离起始日期的天数，取模366是为了处理跨年的循环场景。
3. WHERE子句分同一年和跨年两种场景，确保所有周年纪念日落在指定区间的员工都被筛选出来。

方法二：处理闰年的健壮实现 #

如果员工中有2月29日入职的情况，DAYOFYEAR会存在偏差，此时可以用DATE_ADD和TIMESTAMPDIFF计算准确的周年纪念日：

-- 设置指定的日期范围
SET @start_date = '2023-01-09';
SET @end_date = '2023-09-01';

SELECT 
    EmpID,
    Date_of_joining,
    -- 计算在指定区间内的准确周年纪念日
    CASE 
        WHEN DATE_ADD(Date_of_joining, INTERVAL TIMESTAMPDIFF(YEAR, Date_of_joining, @start_date) YEAR) < @start_date
        THEN DATE_ADD(Date_of_joining, INTERVAL TIMESTAMPDIFF(YEAR, Date_of_joining, @start_date) + 1 YEAR)
        ELSE DATE_ADD(Date_of_joining, INTERVAL TIMESTAMPDIFF(YEAR, Date_of_joining, @start_date) YEAR)
    END AS next_anniversary,
    -- 计算该周年距离起始日期的天数，用于排序
    DATEDIFF(
        CASE 
            WHEN DATE_ADD(Date_of_joining, INTERVAL TIMESTAMPDIFF(YEAR, Date_of_joining, @start_date) YEAR) < @start_date
            THEN DATE_ADD(Date_of_joining, INTERVAL TIMESTAMPDIFF(YEAR, Date_of_joining, @start_date) + 1 YEAR)
            ELSE DATE_ADD(Date_of_joining, INTERVAL TIMESTAMPDIFF(YEAR, Date_of_joining, @start_date) YEAR)
        END,
        @start_date
    ) AS days_from_start
FROM employees
WHERE 
    -- 判断周年纪念日是否落在指定区间内
    CASE 
        WHEN DATE_ADD(Date_of_joining, INTERVAL TIMESTAMPDIFF(YEAR, Date_of_joining, @start_date) YEAR) < @start_date
        THEN DATE_ADD(Date_of_joining, INTERVAL TIMESTAMPDIFF(YEAR, Date_of_joining, @start_date) + 1 YEAR)
        ELSE DATE_ADD(Date_of_joining, INTERVAL TIMESTAMPDIFF(YEAR, Date_of_joining, @start_date) YEAR)
    END BETWEEN @start_date AND @end_date
ORDER BY days_from_start ASC;

逻辑说明： #

1. TIMESTAMPDIFF(YEAR, Date_of_joining, @start_date)计算入职日期到起始日期的年数差，以此为基础计算当前或下一年的周年纪念日。
2. DATE_ADD会自动处理闰年的2月29日：非闰年时，2月29日的周年会自动转为3月1日，确保日期有效性。
3. WHERE子句直接判断计算出的周年纪念日是否落在指定区间内，逻辑更直观。

示例验证 #

用你提供的示例数据，设置@start_date = '2023-01-09'，@end_date = '2023-09-01'，两种方法都会返回以下结果：

和你预期的排序逻辑完全一致。

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