如何获取员工每年的最后日期记录

xxe27gdn 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(328)

我正在编写员工绩效历史详细信息oracle bi报告。我被困在下面的场景中。每个员工每年有多个行。但我只想提取所有员工每年最后一次的记录。
例子：
可用数据：

Employee_number   Effective_date   Name    Performance_rating   Comments  
----------------- ---------------- ------- -------------------- ---------- 
1001              01/01/2017       Emp01   4.0                  Good      
1001              01/06/2017       Emp01   4.0                  Good      
1001              01/12/2017       Emp01   4.0                  Good      
1001              01/01/2018       Emp01   3.8                  Good      
1001              01/06/2018       Emp01   3.8                  Good      
1001              01/02/2019       Emp01   4.0                  Good      
1001              01/08/2019       Emp01   4.0                  Good

在上表中，我只想提取每年的最后一个日期记录。
预期结果：

Employee_number   Effective_date   Name    Performance_rating   Comments  
----------------- ---------------- ------- -------------------- ---------- 
1001              01/12/2017       Emp01   4.0                  Good      
1001              01/06/2018       Emp01   3.8                  Good      
1001              01/08/2019       Emp01   4.0                  Good

请帮忙

sql oracle bi-publisher

来源：https://stackoverflow.com/questions/62444396/how-to-get-last-dated-record-for-each-year-for-an-employee

3条答案

按热度按时间

jckbn6z71#

我会用 ROW_NUMBER 分析函数：

with temp as
  (select employee_number, 
          effective_date,
          name,
          performance_rating,
          comments,
          --
          row_number() over (partition by employee_number, extract (year from effective_date)
                             order by effective_date desc
                            ) rn
   from your_table
  )
select employee_number,
       effective_date,
       name,
       performance_rating,
       comments           
from temp
where rn = 1
order by employee_number, effective_date;

赞(0）回复(0）举报 2021-07-26

iklwldmw2#

尝试使用窗口功能 row_number() 这是演示。

select
    Employee_number,
    Effective_date,
    Name,
    Performance_rating,
    comments
from
(
    select
        Employee_number,
        Effective_date,
        Name,
        Performance_rating,
        comments,
        row_number() over (partition by Employee_number, extract(year from Effective_date) order by Effective_date desc) as rn
    from yourTable
) subq
where rn = 1

赞(0）回复(0）举报 2021-07-26

7z5jn7bk3#

也可以使用相关子查询执行此操作：

select t.*
from t
where t.effective_date = (select max(t2.effective_date)
                          from t t2
                          where t2.employee_number = t.employee_number and
                                trunc(t2.employee_number, 'YYYY') = trunc(t.employee_number, 'YYYY')
                         );

很有意思的是知道哪个更快 row_number() 方法或相关子查询。

赞(0）回复(0）举报 2021-07-26

我来回答

如何获取员工每年的最后日期记录

3条答案

相关问题

热门标签

最新问答