SQL Server内连接后筛选最新日期的技术解决方案问询

嗨，我明白你现在的需求：原来的视图是按employee_id返回单行，但现在需要做内连接后，确保每个员工只保留对应最新修改状态的记录对吧？下面给你几种SQL Server环境下实用的解决方案，你可以根据自己的业务场景选最合适的：

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

这是处理这类"取分组最新记录"需求的标准方法，逻辑清晰，性能也不错。核心思路是给每个employee_id的分组记录按修改日期排序，然后筛选出排名第1的最新记录。

CREATE OR ALTER VIEW YourViewName -- 替换成你的视图名
AS
WITH RankedEmployees AS (
    SELECT 
        em.Employee_ID,
        em.Branch_ID,
        em.ModifyDatetime AS EmployeeMasterModified,
        prm.ModifyDatetime AS PRMasterModified,
        prl.ModifyDatetime AS EmployeePRLocalModified,
        prs.ModifyDatetime AS EmployeePRStateModified,
        -- 继续添加你需要的其他字段
        -- 按员工ID分组，以所有关联表中最晚的修改时间排序，分配排名
        ROW_NUMBER() OVER (
            PARTITION BY em.Employee_ID 
            ORDER BY 
                GREATEST(em.ModifyDatetime, prm.ModifyDatetime, prl.ModifyDatetime, prs.ModifyDatetime) DESC
        ) AS RowRank
    FROM 
        dbo.EmployeeMaster em
    INNER JOIN 
        dbo.EmployeePRMaster prm ON em.Employee_ID = prm.Employee_ID
    INNER JOIN 
        dbo.EmployeePRLocal prl ON em.Employee_ID = prl.Employee_ID
    INNER JOIN 
        dbo.EmployeePRState prs ON em.Employee_ID = prs.Employee_ID
    -- 如果还有其他关联表，继续在这里加INNER JOIN
)
SELECT 
    Employee_ID,
    Branch_ID,
    EmployeeMasterModified,
    PRMasterModified,
    EmployeePRLocalModified,
    EmployeePRStateModified
    -- 其他需要的字段
FROM 
    RankedEmployees
WHERE 
    RowRank = 1; -- 只保留每个员工的最新记录

关键说明： #

• PARTITION BY em.Employee_ID：确保我们是按员工ID分组来计算排名，每个员工单独排序。
• ORDER BY GREATEST(...) DESC：用所有关联表中最晚的修改时间作为排序依据，保证取到的是员工的最新状态。如果你业务上只需要以某一个表的修改时间为准（比如只看EmployeeMaster的更新时间），直接把GREATEST(...)换成对应字段即可，比如em.ModifyDatetime DESC。
• 如果你用的是SQL Server 2017及更早版本，GREATEST函数不支持，那可以用CASE语句来取最大值：

  ORDER BY 
      CASE 
          WHEN em.ModifyDatetime >= prm.ModifyDatetime AND em.ModifyDatetime >= prl.ModifyDatetime AND em.ModifyDatetime >= prs.ModifyDatetime THEN em.ModifyDatetime
          WHEN prm.ModifyDatetime >= prl.ModifyDatetime AND prm.ModifyDatetime >= prs.ModifyDatetime THEN prm.ModifyDatetime
          WHEN prl.ModifyDatetime >= prs.ModifyDatetime THEN prl.ModifyDatetime
          ELSE prs.ModifyDatetime
      END DESC
  

方案2：用TOP 1 WITH TIES（更简洁的写法） #

如果你的SQL Server版本支持（2017及以上），可以用这种更紧凑的写法，效果和方案1完全一致：

CREATE OR ALTER VIEW YourViewName
AS
SELECT TOP 1 WITH TIES
    em.Employee_ID,
    em.Branch_ID,
    em.ModifyDatetime AS EmployeeMasterModified,
    prm.ModifyDatetime AS PRMasterModified,
    prl.ModifyDatetime AS EmployeePRLocalModified,
    prs.ModifyDatetime AS EmployeePRStateModified
    -- 其他字段
FROM 
    dbo.EmployeeMaster em
INNER JOIN 
    dbo.EmployeePRMaster prm ON em.Employee_ID = prm.Employee_ID
INNER JOIN 
    dbo.EmployeePRLocal prl ON em.Employee_ID = prl.Employee_ID
INNER JOIN 
    dbo.EmployeePRState prs ON em.Employee_ID = prs.Employee_ID
-- 其他关联表
ORDER BY 
    ROW_NUMBER() OVER (
        PARTITION BY em.Employee_ID 
        ORDER BY GREATEST(em.ModifyDatetime, prm.ModifyDatetime, prl.ModifyDatetime, prs.ModifyDatetime) DESC
    );

TOP 1 WITH TIES会自动返回所有排名为1的记录，省去了CTE和WHERE筛选的步骤，代码更短。

方案3：先单独取每个表的最新记录，再连接（适合多表多记录场景） #

如果你的每个关联表中，单个员工可能有多条记录，直接内连接会产生笛卡尔积导致数据重复，那可以先给每个表单独取最新记录，再做连接：

CREATE OR ALTER VIEW YourViewName
AS
WITH LatestEmployeeMaster AS (
    SELECT 
        Employee_ID,
        Branch_ID,
        ModifyDatetime AS EmployeeMasterModified,
        ROW_NUMBER() OVER (PARTITION BY Employee_ID ORDER BY ModifyDatetime DESC) AS RowRank
    FROM dbo.EmployeeMaster
),
LatestPRMaster AS (
    SELECT 
        Employee_ID,
        ModifyDatetime AS PRMasterModified,
        ROW_NUMBER() OVER (PARTITION BY Employee_ID ORDER BY ModifyDatetime DESC) AS RowRank
    FROM dbo.EmployeePRMaster
),
LatestPRLocal AS (
    SELECT 
        Employee_ID,
        ModifyDatetime AS EmployeePRLocalModified,
        ROW_NUMBER() OVER (PARTITION BY Employee_ID ORDER BY ModifyDatetime DESC) AS RowRank
    FROM dbo.EmployeePRLocal
),
LatestPRState AS (
    SELECT 
        Employee_ID,
        ModifyDatetime AS EmployeePRStateModified,
        ROW_NUMBER() OVER (PARTITION BY Employee_ID ORDER BY ModifyDatetime DESC) AS RowRank
    FROM dbo.EmployeePRState
)
SELECT 
    lem.Employee_ID,
    lem.Branch_ID,
    lem.EmployeeMasterModified,
    lprm.PRMasterModified,
    lprl.EmployeePRLocalModified,
    lprs.EmployeePRStateModified
FROM 
    LatestEmployeeMaster lem
INNER JOIN 
    LatestPRMaster lprm ON lem.Employee_ID = lprm.Employee_ID AND lprm.RowRank = 1
INNER JOIN 
    LatestPRLocal lprl ON lem.Employee_ID = lprl.Employee_ID AND lprl.RowRank = 1
INNER JOIN 
    LatestPRState lprs ON lem.Employee_ID = lprs.Employee_ID AND lprs.RowRank = 1
WHERE 
    lem.RowRank = 1;

这个方案先把每个表中员工的最新记录筛选出来，再做内连接，彻底避免了笛卡尔积的问题，数据更准确。

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