SQL存储过程：如何为每个公司获取唯一最新经理数据？

问题原因分析 #

• 子查询逻辑错误：原代码的OR条件会同时选中入职最早和入职最晚的员工，而非仅最新的经理。NOT EXISTS(ee.DateHired > e.DateHired)用于筛选最新员工，但NOT EXISTS(ee.DateHired < e.DateHired)会筛选最早员工，两者用OR连接会同时返回这两类数据，导致同一公司出现多条重复记录。
• 子查询未限定职位：原代码的子查询未过滤JobRoleNo_FK=1，会对比公司所有员工的入职时间。如果公司有其他职位的员工入职晚于经理，那么经理的NOT EXISTS(ee.DateHired > e.DateHired)条件会不成立，导致正确的最新经理被排除，反而返回更早的经理数据（比如FooBar公司的Daisy）。
• 数据缺失问题：如果公司的经理入职时间既不是最早也不是最晚（存在其他职位员工入职更早/更晚），原条件无法匹配到该经理，导致公司数据缺失；DISTINCT仅能去重，无法解决逻辑错误导致的根本问题。

解决方法与修正代码 #

方法1：使用窗口函数（推荐） #

利用ROW_NUMBER()按公司分组，仅保留每个公司内职位为经理、入职时间最晚的员工，逻辑清晰且性能稳定：

CREATE PROCEDURE [Current Company Managers] 
AS
WITH ManagerRanked AS (
    SELECT 
        c.CompanyNoPK,
        c.CompanyName,
        e.EmployeeName,
        e.DateHired,
        -- 按公司分组，经理按入职时间倒序排名，最新的排第1
        ROW_NUMBER() OVER (PARTITION BY c.CompanyNoPK ORDER BY e.DateHired DESC) AS RankNum
    FROM [Company] c
    LEFT JOIN [Employee] e 
        ON e.CompanyNo_FK = c.CompanyNoPK 
        AND e.JobRoleNo_FK = 1 -- 仅关联经理职位
)
SELECT 
    CompanyName,
    EmployeeName,
    DateHired
FROM ManagerRanked
WHERE RankNum = 1; -- 仅取每个公司的最新经理

方法2：关联子查询找最大入职时间 #

先查询每个公司经理的最晚入职时间，再关联员工表获取对应信息：

CREATE PROCEDURE [Current Company Managers] 
AS
SELECT 
    c.CompanyName,
    e.EmployeeName,
    e.DateHired
FROM [Company] c
LEFT JOIN (
    -- 先找出每个公司经理的最晚入职时间
    SELECT 
        CompanyNo_FK,
        MAX(DateHired) AS LatestHireDate
    FROM [Employee]
    WHERE JobRoleNo_FK = 1
    GROUP BY CompanyNo_FK
) latestMgr ON latestMgr.CompanyNo_FK = c.CompanyNoPK
-- 关联到对应的员工数据
LEFT JOIN [Employee] e 
    ON e.CompanyNo_FK = c.CompanyNoPK 
    AND e.JobRoleNo_FK = 1
    AND e.DateHired = latestMgr.LatestHireDate;

补充说明 #

• 两种方法都会保留所有公司数据，即使该公司没有经理（对应EmployeeName和DateHired为NULL）。
• 若存在同一公司同一天入职多个经理的情况，可在窗口函数的ORDER BY中加入EmployeeNo等唯一字段，确保结果唯一。

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