如何基于日期查询员工当前职位与薪资？SQL语句纠错

错误原因分析 #

你的SQL存在两个关键问题导致返回历史数据：

• 笔误：子查询的别名是pe，但连接条件中错误写成了pc.empno，导致关联逻辑失效，无法正确匹配最新生效日期的记录
• 关联条件不完整：仅匹配了empno和effdate，未匹配company字段，虽然当前数据中员工不会跨公司，但这会导致逻辑不严谨，在复杂场景下出现错误

修正方案1：修复关联子查询 #

基于你的原有SQL结构修正，修正笔误并补充完整关联条件：

SELECT z.company, em.empno, em.lastname, em.firstname, z.job, z.salary
FROM emp em
JOIN (
    SELECT dj.company, dj.empno, dj.job, dj.salary
    FROM dept_job dj
    JOIN (
        SELECT company, empno, MAX(effdate) AS maxeffdate
        FROM dept_job
        GROUP BY company, empno
    ) pe ON dj.company = pe.company 
        AND dj.empno = pe.empno 
        AND dj.effdate = pe.maxeffdate
) z ON em.empno = z.empno
ORDER BY company, empno;

修正方案2：使用窗口函数（推荐） #

对于现代SQL数据库（MySQL 8+、PostgreSQL、SQL Server等），使用ROW_NUMBER()窗口函数更简洁高效，逻辑更清晰：

SELECT company, empno, lastname, firstname, job, salary
FROM (
    SELECT 
        dj.company,
        em.empno,
        em.lastname,
        em.firstname,
        dj.job,
        dj.salary,
        -- 按员工编号分组，每组内按生效日期降序排序，最新记录标记为1
        ROW_NUMBER() OVER (PARTITION BY em.empno ORDER BY dj.effdate DESC) AS rn
    FROM emp em
    JOIN dept_job dj ON em.empno = dj.empno
) t
WHERE rn = 1
ORDER BY company, empno;

这个方法通过窗口函数为每个员工的所有记录按生效日期排序，直接筛选出排序第一的最新记录，避免了多层关联的复杂逻辑，性能更优。

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