如何在oracle中获取最新的员工记录? [英] How to get the latest employee record in oracle?

问题描述

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

I have a employee table with columns like emp_id, firstname, lastname, region_id, status and effective_date.

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

Employee Table can have multiple entries for same employee with different effective dates and statuses.

员工的状态可以为"Leaver"和"Joiner".

Employee can have two statuses 'Leaver' and 'Joiner'.

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

使用employee表中的上述数据，如果我想获取截至2012年1月1日的詹姆斯的最新记录，我将获得id = 1的记录，

With the above data in employee table, If i want to get the latest record of james as on 1 Jan 2012, I would get record with id = 1,

如果我想获得2012年5月1日的詹姆斯的最新记录，我将获得id = 5的记录

If i want to get the latest record of james as on 1 May 2012, I would get record with id = 5

如果我想获得截至2012年8月1日的詹姆斯的最新记录，我将获得id = 3的记录，

If i want to get the latest record of james as on 1 Aug 2012, I would get record with id = 3,

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

If i want to get the latest record of james as on 1 Sep 2012, I would get record with id = 6

正确地执行查询可以为我提供最新记录

Following query correctly gives me latest record

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

但是然后如何获取其他列，例如firstname，region等.

But then how do I get the other columns such as firstname , region etc.

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

If I put them in select clause or group by clause, I dont just get the latest record but the other records as well.

推荐答案

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的最大有效日期的记录.

Above will get you a record with maximal effective__Date for every distinct emp_id.

您对查询给定日期返回记录的第二个要求应通过此查询完整填写:

Your second requirement of returning record for given date should be fullfiled by this query:

(状态ASC"-如果同一日期也有"Leaver"，将负责获得"Joiner"状态.)

("status ASC" - will take care of taking "Joiner" status if there is also "Leaver" for the same date.)

 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;

这篇关于如何在oracle中获取最新的员工记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！