MySQL如何查询指定年月落在from-to日期区间的员工项目记录

原写法问题 #

你之前使用2022-03-01 between from and to的逻辑，仅判断「查询月的第一天落在员工分配周期内」，会漏掉三类匹配场景：

• 分配周期完全落在当月中间（如2022-03-13至2022-03-23）
• 分配周期从上月跨到当月内结束（如2022-02-10至2022-03-23）
• 分配周期从当月内开始跨到后续月份（如2022-03-15至2022-08-29）

核心判断规则 #

两个时间区间存在交集的充要条件为：区间A的起始时间 <= 区间B的结束时间，且区间A的结束时间 >= 区间B的起始时间，该规则不受跨月、跨年场景影响，无判断遗漏。
你的查询场景里，两个区间分别是：

• 员工分配周期：[FROM, TO]
• 查询目标月份周期：[当月第一天, 当月最后一天]

可直接使用的SQL代码 #

注意FROM是SQL保留关键字，查询时需要加转义符避免语法错误，以查询员工ID=3、2022年3月的数据为例，基础写法如下：

SELECT ID, FK_PROJECT, FK_EMPLOYEE, `FROM`, `TO`
FROM 你的关联表名
WHERE
  FK_EMPLOYEE = 3
  -- 区间交集判断核心逻辑
  AND `FROM` <= '2022-03-31'
  AND `TO` >= '2022-03-01';

如果你需要从传入的任意日期参数自动计算月首、月末，可以使用对应数据库的内置日期函数，无需手动拼接日期：

• MySQL 版本示例：

-- 定义传入参数，可由业务代码直接传值替换
SET @query_emp_id = 3;
SET @input_date = '2022-03-任意日期'; -- 传入的完整日期参数
SELECT ID, FK_PROJECT, FK_EMPLOYEE, `FROM`, `TO`
FROM 你的关联表名
WHERE
  FK_EMPLOYEE = @query_emp_id
  AND `FROM` <= LAST_DAY(@input_date) -- LAST_DAY()直接返回传入日期所在月的最后一天
  AND `TO` >= DATE_FORMAT(@input_date, '%Y-%m-01'); -- 格式化得到传入日期所在月的第一天

• PostgreSQL 版本示例：

SELECT id, fk_project, fk_employee, "FROM", "TO"
FROM 你的关联表名
WHERE
  fk_employee = 3
  AND "FROM" <= (date_trunc('month', DATE '2022-03-01') + interval '1 month -1 day')::date
  AND "TO" >= date_trunc('month', DATE '2022-03-01')::date;

边界场景验证 #

该逻辑可覆盖所有特殊场景，无匹配遗漏：

• 分配周期完全在当月内：起止日期均在当月1日到月末之间，两个判断条件均成立，正常返回
• 分配周期跨月/跨年：只要和目标月份有重叠，就会满足判断条件被返回
• 分配周期完全覆盖目标月份：起止日期分别早于月首、晚于月末，条件成立正常返回
• 分配周期和目标月份无重叠：要么结束日期早于月首，要么开始日期晚于月末，条件不成立不会被返回

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