如何编写SQL查询日期范围内存在日期冲突的重复id_employee数据

要找出指定日期范围内存在时间重叠的重复员工记录，核心是判断同一员工的多条时间区间是否有交集，同时筛选出落在目标范围内的冲突记录。下面提供两种实用的解决方案：

方法1：自连接查询（直观易懂） #

这种方法通过将表与自身连接，匹配同一员工的不同记录，并判断时间区间是否重叠，最后筛选目标日期范围的冲突。

假设你的表名为employee_records，指定的目标日期范围为@target_start（比如'01/04/2018'）和@target_end（比如'08/04/2018'），SQL语句如下（以MySQL为例，其他数据库可调整日期转换函数）：

SELECT DISTINCT 
    e1.id_employee,
    e1.start_date AS emp1_start,
    e1.enddate AS emp1_end,
    e2.start_date AS emp2_start,
    e2.enddate AS emp2_end
FROM employee_records e1
JOIN employee_records e2 
    ON e1.id_employee = e2.id_employee
    AND e1.id <> e2.id  -- 排除同一条记录的自匹配
    -- 判断两个时间区间是否重叠
    AND STR_TO_DATE(e1.start_date, '%d/%m/%Y') <= STR_TO_DATE(e2.enddate, '%d/%m/%Y')
    AND STR_TO_DATE(e1.enddate, '%d/%m/%Y') >= STR_TO_DATE(e2.start_date, '%d/%m/%Y')
    -- 确保冲突记录与目标日期范围有交集
    AND (
        STR_TO_DATE(e1.start_date, '%d/%m/%Y') BETWEEN STR_TO_DATE(@target_start, '%d/%m/%Y') AND STR_TO_DATE(@target_end, '%d/%m/%Y')
        OR STR_TO_DATE(e1.enddate, '%d/%m/%Y') BETWEEN STR_TO_DATE(@target_start, '%d/%m/%Y') AND STR_TO_DATE(@target_end, '%d/%m/%Y')
        OR STR_TO_DATE(e2.start_date, '%d/%m/%Y') BETWEEN STR_TO_DATE(@target_start, '%d/%m/%Y') AND STR_TO_DATE(@target_end, '%d/%m/%Y')
        OR STR_TO_DATE(e2.enddate, '%d/%m/%Y') BETWEEN STR_TO_DATE(@target_start, '%d/%m/%Y') AND STR_TO_DATE(@target_end, '%d/%m/%Y')
    );

逻辑说明： #

• e1.id <> e2.id：避免同一条记录和自身匹配，确保比较的是不同的记录。
• 日期重叠判断：两条区间[s1, e1]和[s2, e2]重叠的条件是s1 <= e2 AND e1 >= s2，覆盖了包含、交叉等所有重叠场景。
• 目标范围筛选：只要任意一条记录的时间区间和目标范围有交集，就会被纳入结果。

方法2：窗口函数查询（高效适合大数据量） #

如果你的数据量较大，窗口函数的方式性能更优，它通过LAG函数获取同一员工的上一条记录的结束日期，直接比较是否与当前记录的开始日期重叠。

WITH sorted_records AS (
    SELECT 
        id_employee,
        start_date,
        enddate,
        -- 获取同一员工按开始日期排序后的上一条记录的结束日期
        LAG(STR_TO_DATE(enddate, '%d/%m/%Y')) OVER (
            PARTITION BY id_employee 
            ORDER BY STR_TO_DATE(start_date, '%d/%m/%Y')
        ) AS prev_enddate
    FROM employee_records
    -- 先筛选出和目标范围有交集的记录，减少后续计算量
    WHERE STR_TO_DATE(start_date, '%d/%m/%Y') <= STR_TO_DATE(@target_end, '%d/%m/%Y')
      AND STR_TO_DATE(enddate, '%d/%m/%Y') >= STR_TO_DATE(@target_start, '%d/%m/%Y')
)
SELECT 
    id_employee,
    start_date,
    enddate,
    DATE_FORMAT(prev_enddate, '%d/%m/%Y') AS prev_enddate
FROM sorted_records
-- 上一条记录的结束日期 >= 当前记录的开始日期，说明时间重叠
WHERE prev_enddate >= STR_TO_DATE(start_date, '%d/%m/%Y');

逻辑说明： #

• PARTITION BY id_employee：按员工分组，确保只比较同一员工的记录。
• ORDER BY start_date：对每个员工的记录按开始日期排序，方便获取上一条记录。
• LAG(enddate)：获取上一条记录的结束日期，直接和当前记录的开始日期比较，判断是否重叠。

针对你的测试数据的结果 #

用你的测试数据执行上述查询，指定日期范围为'01/04/2018'到'08/04/2018'，会得到：

• 员工A的两条重叠记录（01/04/2018-05/04/2018 和 02/04/2018-05/04/2018）
• 员工B的两条重叠记录（03/04/2018-08/04/2018 和 05/04/2018-08/04/2018）

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