如何通过SQL Join查询员工当前生效的唯一项目？

现有employee表与project表为一对多关系，project表包含字段：id（主键）、emp_id（外键关联employee表）、effective_date（项目生效日期）。需求是编写SQL Join语句，仅返回每个员工当前生效的唯一项目——即排除生效日期晚于当前日期的项目，且取每个员工最近生效的那一个。

示例数据 #

假设某员工的项目记录如下：

当当前日期为2024-01-22时，需排除未来生效的项目3，仅返回id为2的项目。

实现方案 #

方案1：窗口函数（通用推荐）

这是最简洁且兼容多数现代数据库的写法，通过窗口函数按员工分组排序，筛选出每个员工最近的有效项目：

SELECT 
    e.*, 
    p.id AS project_id, 
    p.effective_date AS current_effective_date
FROM employee e
JOIN (
    SELECT 
        emp_id,
        id,
        effective_date,
        -- 按员工分组，生效日期倒序排，取每组第一条
        ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) AS rn
    FROM project
    -- 过滤掉未来生效的项目
    WHERE effective_date <= CURRENT_DATE
) p ON e.id = p.emp_id
WHERE p.rn = 1;

方案2：关联子查询（兼容老版本数据库）

如果数据库不支持窗口函数，可以用子查询获取每个员工的最近有效日期，再匹配对应项目：

SELECT 
    e.*, 
    p.id AS project_id, 
    p.effective_date AS current_effective_date
FROM employee e
JOIN project p ON e.id = p.emp_id
WHERE 
    p.effective_date <= CURRENT_DATE
    AND p.effective_date = (
        SELECT MAX(effective_date)
        FROM project
        WHERE emp_id = p.emp_id AND effective_date <= CURRENT_DATE
    );

注意事项 #

• CURRENT_DATE是通用的当前日期函数，不同数据库可能有替代写法：MySQL用CURDATE()，SQL Server用GETDATE()，Oracle用SYSDATE。
• 若需要返回所有员工（包括无当前生效项目的员工），将JOIN替换为LEFT JOIN即可。

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