SQL Server：筛选最新状态为Active的员工数据（修正查询语句）

表结构与示例数据 #

Employee表 #

EmployeeHistory表 #

需求 #

筛选出存在EmployeeHistory记录且最新状态为Active的员工（示例中应为John和Allan），要求每个员工仅返回一条记录，显示其最新状态。

原查询的问题 #

你编写的查询会返回符合条件员工的所有历史记录（比如John会出现3条），而非仅返回最新的那条，不符合预期输出要求。

修正后的查询方案 #

方案1：使用窗口函数ROW_NUMBER()（推荐） #

通过窗口函数为每个员工的历史记录按Id倒序编号，取编号为1的最新记录，再关联员工表筛选状态为Active的记录：

WITH LatestEmployeeStatus AS (
    SELECT 
        EmployeeId,
        Status,
        ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY Id DESC) AS RowNum
    FROM EmployeeHistory
)
SELECT 
    E.Id,
    E.Name,
    LES.Status
FROM Employee E
INNER JOIN LatestEmployeeStatus LES ON E.Id = LES.EmployeeId
WHERE LES.RowNum = 1 AND LES.Status = 'Active';

方案2：先获取每个员工的最新历史记录ID再关联 #

先查询每个员工的最大历史记录Id，再关联获取对应状态，最后筛选Active：

SELECT 
    E.Id,
    E.Name,
    EH.Status
FROM Employee E
INNER JOIN (
    SELECT EmployeeId, MAX(Id) AS LatestId
    FROM EmployeeHistory
    GROUP BY EmployeeId
) LatestEH ON E.Id = LatestEH.EmployeeId
INNER JOIN EmployeeHistory EH ON LatestEH.LatestId = EH.Id
WHERE EH.Status = 'Active';

方案3：使用TOP 1 WITH TIES（简洁写法） #

利用TOP 1 WITH TIES结合窗口函数直接获取每个员工的最新记录，再筛选状态：

SELECT TOP 1 WITH TIES
    E.Id,
    E.Name,
    EH.Status
FROM Employee E
INNER JOIN EmployeeHistory EH ON E.Id = EH.EmployeeId
ORDER BY ROW_NUMBER() OVER (PARTITION BY E.Id ORDER BY EH.Id DESC)
HAVING EH.Status = 'Active';

说明 #

以上三种方案均能实现需求：仅返回存在历史记录且最新状态为Active的员工，每个员工对应一条记录。其中窗口函数方案逻辑清晰、易于维护，适配大多数业务场景。

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