如何查询验证dept_emp表中员工任职时间段无重叠？

背景 #

我正在学习Employees示例数据库，该数据库包含dept_emp表，表结构定义如下：

create table dept_emp
(
    emp_no    int     not null,
    dept_no   char(4) not null,
    from_date date    not null,
    to_date   date    not null,
    primary key (emp_no, dept_no),
    constraint dept_emp_ibfk_1
        foreign key (emp_no) references employees (emp_no)
            on delete cascade,
    constraint dept_emp_ibfk_2
        foreign key (dept_no) references departments (dept_no)
            on delete cascade
);

create index dept_no
    on dept_emp (dept_no)

此外数据库中还定义了两个视图，暗示员工的部门任职时间段无重叠：

create definer = root@localhost view current_dept_emp as
select `employees`.`l`.`emp_no`    AS `emp_no`,
       `d`.`dept_no`               AS `dept_no`,
       `employees`.`l`.`from_date` AS `from_date`,
       `employees`.`l`.`to_date`   AS `to_date`
from (`employees`.`dept_emp` `d` join `employees`.`dept_emp_latest_date` `l`
      on (((`d`.`emp_no` = `employees`.`l`.`emp_no`) and (`d`.`from_date` = `employees`.`l`.`from_date`) and
           (`employees`.`l`.`to_date` = `d`.`to_date`))));

create definer = root@localhost view dept_emp_latest_date as
select `employees`.`dept_emp`.`emp_no`         AS `emp_no`,
       max(`employees`.`dept_emp`.`from_date`) AS `from_date`,
       max(`employees`.`dept_emp`.`to_date`)   AS `to_date`
from `employees`.`dept_emp`
group by `employees`.`dept_emp`.`emp_no`;

以下是针对员工编号499018的查询语句及结果：
查询语句：

select *
from dept_emp
where emp_no = 499018;

查询结果：

emp_no dept_no  from_date    to_date
------------------------------------
499018    d004 1996-03-25 9999-01-01
499018    d009 1994-08-28 1996-03-25

问题 #

如何编写查询语句，验证数据库中每个emp_no对应的from_date和to_date时间段不存在重叠？

解决方案 #

方法一：自连接查询 #

通过自连接dept_emp表，对比同一员工的不同任职记录，直接筛选出存在重叠的时间段：

SELECT
    a.emp_no,
    a.dept_no AS dept_no_1,
    a.from_date AS from_date_1,
    a.to_date AS to_date_1,
    b.dept_no AS dept_no_2,
    b.from_date AS from_date_2,
    b.to_date AS to_date_2
FROM
    dept_emp a
JOIN
    dept_emp b ON a.emp_no = b.emp_no 
                AND a.dept_no != b.dept_no
                AND a.from_date < b.to_date
                AND b.from_date < a.to_date;

逻辑说明：

• 自连接同一员工的不同部门任职记录
• a.dept_no != b.dept_no避免同一部门的重复记录匹配
• 核心条件a.from_date < b.to_date AND b.from_date < a.to_date判断时间段重叠：若A的开始时间早于B的结束时间，且B的开始时间早于A的结束时间，说明两个时间段交叉

如果查询返回空结果，说明所有员工的任职时间段均无重叠；返回的记录即为存在重叠的任职信息。

方法二：窗口函数查询 #

利用窗口函数按员工分组排序后，检查当前记录的开始时间是否早于上一条记录的结束时间：

SELECT
    emp_no,
    dept_no,
    from_date,
    to_date,
    LAG(to_date) OVER (PARTITION BY emp_no ORDER BY from_date) AS prev_to_date
FROM
    dept_emp
HAVING
    from_date < prev_to_date;

逻辑说明：

• 按emp_no分组、from_date排序，用LAG(to_date)获取同一员工上一条任职记录的结束时间
• 若当前记录的开始时间早于上一条的结束时间，说明存在时间段重叠

同样，空结果代表无重叠，返回记录即为有问题的任职数据。

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