sql - oracle如何获取最新的员工记录？

Question

我有一个雇员表，其中包含 emp_id、名字、姓氏、region_id、状态和有效日期等列。

员工表可以为具有不同生效日期和状态的同一员工提供多个条目。

员工可以有两种状态“离开”和“入职”。

id     emp_id    firstname     region    status     effective_date  
1       1         James        Asia      Joiner     1-Jan-2012 
2       1         James        UK        Leaver     1-Aug-2012
3       1         James        USA       Joiner     1-Aug-2012
4       1         James        Asia      Leaver     1-May-2012
5       1         James        UK        Joiner     1-May-2012
6       1         James        USA       Leaver     1-Sep-2012

根据员工表中的上述数据，如果我想获取 james 在 2012 年 1 月 1 日的最新记录，我将获得 id = 1 的记录，

如果我想获得 james 在 2012 年 5 月 1 日的最新记录，我会得到 id = 5 的记录

如果我想获得 james 在 2012 年 8 月 1 日的最新记录，我将获得 id = 3 的记录，

如果我想获得 james 在 2012 年 9 月 1 日的最新记录，我将获得 id = 6 的记录

以下查询正确地为我提供了最新记录

SELECT 
        emp_id, 
        MAX(effective_date) AS latest_effective_date
FROM 
        EMPLOYEE
GROUP BY 
        emp_id

但是我如何获得其他列，例如 firstname ， region 等。

如果我将它们放在 select 子句或 group by 子句中，我不仅会得到最新的记录，还会得到其他记录。

Answer 1

SELECT * FROM 
( SELECT  
    e.*,
    ROW_NUMBER() OVER (partition by emp_id order by effective_date DESC) r
FROM  
    EMPLOYEE  e)
WHERE r = 1;

以上将为您提供每个不同 emp_id 的最大 effective__Date 的记录。

您返回给定日期的记录的第二个要求应该由这个查询来完成:

(“状态 ASC” - 如果同一日期也有“离开者”，则将处理“加入者”状态。)

 SELECT * FROM 
( SELECT  
    e.*,
    ROW_NUMBER() OVER (partition by emp_id order by effective_date DESC, status ASC) r
FROM  
    EMPLOYEE  e
WHERE effective_date <= '<your desired date>')
WHERE r=1;