无主键/外键时通过日期关联表，查询入职早于经理的员工

在EMP_NO数据损坏无法使用的情况下，推断管理人员身份，并编写SQL查询找出入职时间早于对应经理的员工。以下是数据库Schema及测试数据，同时附上了无法正常运行的查询语句，需要完善逻辑并修正查询。

数据库Schema及测试数据 #

CREATE TABLE Departments (
    dept_no VARCHAR(5) NOT NULL,
    dept_name VARCHAR(255) NOT NULL
);

INSERT INTO Departments (dept_no, dept_name) VALUES
    ('d009', 'Customer Service');
CREATE TABLE Dept_emp (
    EMP_NO INT NOT NULL,
    DEPT_NO VARCHAR(5) NOT NULL,
    FROM_DATE DATE NOT NULL,
    TO_DATE DATE NOT NULL
);

INSERT INTO Dept_emp (EMP_NO, DEPT_NO, FROM_DATE, TO_DATE) VALUES
    (10001, 'd005', '1986-06-26', '9999-01-01'),
    (10002, 'd007', '1996-08-03', '9999-01-01');
CREATE TABLE Dept_manager (
    EMP_NO INT NOT NULL,
    DEPT_NO VARCHAR(5) NOT NULL,
    FROM_DATE DATE NOT NULL,
    TO_DATE DATE NOT NULL
);

INSERT INTO Dept_manager (EMP_NO, DEPT_NO, FROM_DATE, TO_DATE) VALUES
    (110022, 'd001', '1985-01-01', '1991-10-01');
CREATE TABLE Employees (
    EMP_NO INT NOT NULL,
    BIRTH_DATE DATE NOT NULL,
    FIRST_NAME VARCHAR(255) NOT NULL,
    LAST_NAME VARCHAR(255) NOT NULL,
    GENDER CHAR(1) NOT NULL,
    HIRE_DATE DATE NOT NULL
);

INSERT INTO Employees (EMP_NO, BIRTH_DATE, FIRST_NAME, LAST_NAME, GENDER, HIRE_DATE) VALUES
    (1, '1977-06-14', 'Geert', 'Vanderkelen', 'M', '2018-04-22'),
    (10001, '1953-09-02', 'Georgi', 'Facello', 'M', '1986-06-26');
CREATE TABLE Salaries (
    EMP_NO INT NOT NULL,
    SALARY INT NOT NULL,
    FROM_DATE DATE NOT NULL,
    TO_DATE DATE NOT NULL
);

-- Insert your sample data into the Salaries table here
CREATE TABLE Titles (
    EMP_NO INT NOT NULL,
    TITLE VARCHAR(255) NOT NULL,
    FROM_DATE DATE NOT NULL,
    TO_DATE DATE NOT NULL
);

-- Insert your sample data into the Titles table here

INSERT INTO Departments (dept_no, dept_name) VALUES
    ('d001', 'Human Resources'),
    ('d002', 'Finance'),
    ('d003', 'Marketing'),
    ('d004', 'Engineering'),
    ('d005', 'Sales');
    
INSERT INTO Dept_emp (EMP_NO, DEPT_NO, FROM_DATE, TO_DATE) VALUES
    (10003, 'd001', '1992-05-05', '9999-01-01'),
    (10004, 'd002', '1994-06-15', '9999-01-01'),
    (10005, 'd003', '1995-04-12', '9999-01-01'),
    (10006, 'd004', '1997-10-17', '9999-01-01'),
    (10007, 'd005', '1999-12-24', '9999-01-01');
    
INSERT INTO Employees (EMP_NO, BIRTH_DATE, FIRST_NAME, LAST_NAME, GENDER, HIRE_DATE) VALUES
    (10008, '1980-03-25', 'Sarah', 'Johnson', 'F', '2000-08-10'),
    (10009, '1975-12-19', 'Michael', 'Smith', 'M', '2001-02-15'),
    (10010, '1982-09-30', 'Emily', 'Davis', 'F', '2002-04-20'),
    (10011, '1978-06-08', 'David', 'Anderson', 'M', '2003-07-05'),
    (10012, '1985-01-14', 'Jennifer', 'Brown', 'F', '2004-09-30');
    
INSERT INTO Salaries (EMP_NO, SALARY, FROM_DATE, TO_DATE) VALUES
    (10003, 60000, '1992-05-05', '1999-12-31'),
    (10004, 75000, '1994-06-15', '1999-12-31'),
    (10005, 80000, '1995-04-12', '1999-12-31'),
    (10006, 90000, '1997-10-17', '1999-12-31'),
    (10007, 85000, '1999-12-24', '1999-12-31');

INSERT INTO Titles (EMP_NO, TITLE, FROM_DATE, TO_DATE) VALUES
    (10003, 'Manager', '1992-05-05', '1999-12-31'),
    (10004, 'Director', '1994-06-15', '1999-12-31'),
    (10005, 'Senior Manager', '1995-04-12', '1999-12-31'),
    (10006, 'Engineer', '1997-10-17', '1999-12-31'),
    (10007, 'Analyst', '1999-12-24', '1999-12-31');

现有错误查询语句 #

with md as (SELECT 
  e.EMP_NO AS employee_emp_no,
  e.FIRST_NAME AS employee_first_name,
  e.LAST_NAME AS employee_last_name,
  de.DEPT_NO,
  de.from_date de_from_date,
  de.to_date de_to_date,
  dm.from_date as dm_from_date,
  dm.dept_no,
  dm.emp_no as manager_id,
  s.salary
FROM employees e
LEFT JOIN dept_emp de ON e.EMP_NO = de.EMP_NO
LEFT JOIN departments d ON de.DEPT_NO = d.DEPT_NO
LEFT JOIN dept_manager dm ON de.FROM_DATE  = dm.FROM_DATE  and de.to_date = dm.to_date  
left join  salaries s on e.emp_no = s.emp_no
where manager_id is not null )
select distinct e.EMP_NO, e.FIRST_NAME, e.LAST_NAME 
from employees e
LEFT JOIN dept_emp de ON e.EMP_NO = de.EMP_NO
left join md on e.EMP_NO = md.employee_emp_no
where e.hire_date < md.dm_from_date ;

问题分析与解决方案 #

原查询核心问题 #

1. 关联逻辑错误：通过de.FROM_DATE = dm.FROM_DATE and de.to_date = dm.to_date关联员工与经理，要求两者任职时间完全重合，不符合实际场景（员工和经理的任职周期通常部分重叠而非完全一致）。
2. 数据混淆：CTEmd同时关联员工和经理数据，导致后续关联重复，且错误使用经理的任职开始时间代替经理的入职时间进行比较。
3. 关联维度缺失：未按部门关联员工与经理，导致跨部门匹配错误。

管理人员身份推断逻辑（EMP_NO损坏时） #

当EMP_NO字段不可用时，通过以下规则推断管理人员：

• 从Titles表筛选包含Manager、Senior Manager、Director等管理类头衔的记录
• 结合Dept_emp表关联对应部门，确定管理人员的管辖范围
• 若Dept_manager表数据可用，优先使用该表的官方经理记录；若不可用，则用「头衔+部门」的组合匹配员工与经理

修正后的查询语句 #

场景1：Dept_manager表数据可用（优先选择）

该场景下直接使用官方经理记录，匹配同一部门且任职时间段重叠的员工与经理：

WITH EmployeeManager AS (
    SELECT 
        e.emp_no AS employee_no,
        e.first_name AS employee_first,
        e.last_name AS employee_last,
        e.hire_date AS employee_hire_date,
        m.hire_date AS manager_hire_date
    FROM Employees e
    JOIN Dept_emp de ON e.emp_no = de.emp_no
    -- 关联同一部门、任职时间段重叠的经理
    JOIN Dept_manager dm ON de.dept_no = dm.dept_no
        AND de.from_date <= dm.to_date
        AND de.to_date >= dm.from_date
    JOIN Employees m ON dm.emp_no = m.emp_no
    -- 排除员工自身是经理的情况
    WHERE e.emp_no != m.emp_no
)
SELECT DISTINCT
    employee_no,
    employee_first,
    employee_last
FROM EmployeeManager
WHERE employee_hire_date < manager_hire_date;

场景2：EMP_NO损坏，仅通过头衔推断管理人员

当EMP_NO无法使用时，基于头衔识别管理人员并匹配对应部门的员工：

WITH Managers AS (
    -- 筛选管理头衔的人员及对应部门、任职周期
    SELECT 
        t.emp_no AS manager_no,
        de.dept_no,
        e.hire_date AS manager_hire_date,
        t.from_date AS manager_title_from,
        t.to_date AS manager_title_to
    FROM Titles t
    JOIN Employees e ON t.emp_no = e.emp_no
    JOIN Dept_emp de ON t.emp_no = de.emp_no
    WHERE t.title IN ('Manager', 'Senior Manager', 'Director')
),
EmployeeManager AS (
    -- 关联同一部门、任职时间段重叠的员工与经理
    SELECT 
        e.emp_no AS employee_no,
        e.first_name AS employee_first,
        e.last_name AS employee_last,
        e.hire_date AS employee_hire_date,
        m.manager_hire_date
    FROM Employees e
    JOIN Dept_emp de ON e.emp_no = de.emp_no
    JOIN Managers m ON de.dept_no = m.dept_no
        AND de.from_date <= m.manager_title_to
        AND de.to_date >= m.manager_title_from
    WHERE e.emp_no != m.manager_no
)
SELECT DISTINCT
    employee_no,
    employee_first,
    employee_last
FROM EmployeeManager
WHERE employee_hire_date < manager_hire_date;

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