时间戳

首页 > 解决方案 > 获取员工的最后国籍

sql-server - 获取员工的最后国籍

问题描述

我正在尝试获取员工名单及其国籍：

 select concat([Firstname],[Lastname]) as 'Full name',[C].[Label] as 'Nationality' FROM [Employee] [E]
  left join [AF_AdminFile] [AFA] on E.AdminFileId=AFA.AdminFileId
  left join  [AF_Nationality] [AFN] on AFN.AdminFileId= AFA.AdminFileId
  left join [Country] [C] on AFN.CountryId=C.ID

结果，我得到以下信息：

在此处输入图像描述

在这种情况下，员工已经改变了他的国籍，所以Employee表中有一个名为的属性UpdatedDate与 NULL 不同（一旦改变了国籍）

当我在查询中添加日期条件max(UpdatedDate)以获取“DavidFELTEN”的最后一条记录时，我得到与上图相同的结果：

 select concat([Firstname],[Lastname]),[C].[Label] as 'Nationality' FROM [Employee] [E]
  left join [AF_AdminFile] [AFA] on E.AdminFileId=AFA.AdminFileId
  left join  [AF_Nationality] [AFN] on AFN.AdminFileId= AFA.AdminFileId
  left join [Country] [C] on AFN.CountryId=C.ID
  where [E].UpdatedDate = (select max([Emp].UpdatedDate) from [Employee] [Emp]
                            where [Emp].EmployeeId=[E].EmployeeId)

标签： sql-server

解决方案

使用ROW_NUMBER：

WITH cte AS (
    SELECT CONCAT([Firstname], [Lastname]) AS full_name,
        [C].[Label] AS Nationality,
        ROW_NUMBER() OVER (PARTITION BY E.EmployeeId ORDER BY UpdateDate DESC) rn
    FROM [Employee] [E]
    LEFT JOIN [AF_AdminFile] [AFA]
        ON E.AdminFileId = AFA.AdminFileId
    LEFT JOIN [AF_Nationality] [AFN]
        ON AFN.AdminFileId = AFA.AdminFileId
    LEFT JOIN [Country] [C]
        ON AFN.CountryId = C.ID
)

SELECT
    full_name,
    Nationality
FROM cte
WHERE rn = 1;

推荐阅读