SpringBoot如何编写X个工作日过滤的SQL/JPQL/JPA查询语句

首先先指出你现有代码的两个明显问题：

• 实体类上的@Formula注解内的内容是数据库侧执行的SQL片段，你当前写的getWeekDaysCount是Java类方法，数据库无法识别，这个写法完全不生效。
• 你写的工作日计数逻辑有bug：把周五、周六都判定为非工作日，和需求里「周一至周五为工作日」的规则冲突，后续不管用哪种方案都要先修正这个判断。

方案1：预计算时间阈值查询（首推，性能最高） #

不要逐行计算每条记录和当前时间的工作日差，反过来在业务层先算出阈值时间：从当前时间往前推X个工作日，得到一个临界时间点，所有installationScheduleDatetime早于等于这个临界时间的记录，就自然满足「间隔满X个工作日」的要求。
这个方案不需要写任何数据库自定义函数，能直接走时间字段的索引，哪怕表数据量到百万级也能快速查询，完全适配定时任务的场景。

首先写阈值计算的工具方法，已经修正了工作日判断逻辑：

/**
 * 计算N个工作日前的时间临界值
 * @param configWorkDays 配置的间隔工作日数X
 * @return 临界时间，安装时间早于等于该值即满足核验条件
 */
private LocalDateTime getThresholdTime(int configWorkDays) {
    LocalDateTime threshold = LocalDateTime.now();
    int remain = configWorkDays;
    while (remain > 0) {
        threshold = threshold.minusDays(1);
        DayOfWeek dayOfWeek = threshold.getDayOfWeek();
        // 仅排除周六、周日，周一到周五均计为工作日
        if (dayOfWeek != DayOfWeek.SATURDAY && dayOfWeek != DayOfWeek.SUNDAY) {
            remain--;
        }
    }
    return threshold;
}

查询逻辑非常简单，不管用Spring Data JPA的方法派生还是JPQL都可以实现：

• 方法派生写法，直接在Repository接口里定义：

List<InstallDates> findAllByInstallationScheduleDatetimeLessThanEqual(LocalDateTime thresholdTime);

• JPQL写法：

@Query("SELECT i FROM InstallDates i WHERE i.installationScheduleDatetime <= :thresholdTime")
List<InstallDates> queryMatchedRecords(@Param("thresholdTime") LocalDateTime thresholdTime);

定时任务执行时，先读取配置的X工作日值，调用工具方法算出阈值，直接传入查询方法即可拿到所有符合条件的记录。

方案2：数据库自定义函数计算（适合小数据量场景） #

如果一定要在数据库侧完成工作日计算，需要先在你用的数据库里创建同名的自定义函数，@Formula注解才能正常生效。
以MySQL为例，先执行如下SQL创建工作日计数函数：

DELIMITER //
CREATE FUNCTION getWeekDaysCount(startTime DATETIME)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE workDayTotal INT DEFAULT 0;
    DECLARE cur DATE DEFAULT DATE(startTime);
    DECLARE today DATE DEFAULT CURDATE();
    WHILE cur <= today DO
        -- MySQL中DAYOFWEEK返回1=周日，2=周一...7=周六，排除周日、周六
        IF DAYOFWEEK(cur) NOT IN (1,7) THEN
            SET workDayTotal = workDayTotal + 1;
        END IF;
        SET cur = DATE_ADD(cur, INTERVAL 1 DAY);
    END WHILE;
    RETURN workDayTotal;
END //
DELIMITER ;

函数创建完成后，你实体里的@Formula("getWeekDaysCount(installationScheduleDatetime)")就可以正常映射工作日计数字段，查询时直接加过滤条件即可：

@Query("SELECT i FROM InstallDates i WHERE i.weekDaysCount >= :configWorkDays")
List<InstallDates> queryByWorkDayCount(@Param("configWorkDays") Integer configWorkDays);

注意这个方案的缺陷：自定义函数计算无法命中installation_schedule_datetime字段的索引，查询时会做全表逐行计算，表数据量超过1万条后查询速度会明显变慢，不适合数据量持续增长的业务场景。

其他注意事项 #

• 你写在实体类里的Java版getWeekDaysCount方法和JPA的@Formula没有任何关联，JPA不会调用Java实体类的方法来计算@Formula字段，这个方法可以直接删除。
• 如果业务不需要精确到时、分、秒，可以在计算时把LocalDateTime截断为LocalDate再做比较，避免同一天内不同时间点导致的判断误差。
• 如果后续要加法定节假日判断，只需要在方案1的阈值计算逻辑里，维护一个节假日列表，遇到节假日额外减1天即可，不需要改数据库逻辑，扩展性更好。

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