如何在sql中获取员工的最后记录？ [英] How to get last records of employees in sql?

问题描述

SalId EmpId TakenSal TakenDate AvailSal CompanyId

13 68 1000 02-Jul-2015 29000 1021

14 68 100 02-Jul-2015 28900 1021

15 69 1000 02-Jul-2015 23000 1021

16 82 1000 06-Jul-2015 19000 1024

17 82 1000 06-Jul-2015 18000 1024

18 83 1000 06-Jul-2015 19000 1024

19 83 1000 06-Jul-2015 18000 1024

20 82 1000 06-Jul- 2015 17000 1024

21 82 1000 06-Jul-2015 16000 1024

22 82 1000 06-Jul-2015 15000 1024

23 82 1000 06-Jul-2015 14000 1024

24 94 1000 09-Jul-2015 19000 1014

25 94 1000 09-Jul-2015 18000 1014

26 94 1000 09-Jul-2015 17000 1014

27 94 1000 09-Jul-2015 18000 1014



如何在sql中获得68,69,82,83,94名员工的最后记录？

解决方案

按排序SalId 降序然后使用 TOP 关键字来获得最上面一行。

这将是你的最后一条记录！

 选择 em。* 来自员工em  join （
 选择 max（SalId） as  SalId，EmpId 来自员工
  group   by  EmpId）  s   em.SalId = s.SalId 和 em.EmpId = s.EmpId 

或

从[tbl_emps]中选择*，其中[alid] in（SELECT max（alId）FROM [tbl_emps] group by empid）

尝试如下

  SELECT  *  FROM  
（ SELECT  EmpId，SalId，
 DENSE_RANK（） OVER （ PARTITION   BY  SalId  ORDER   BY  EmpId  DESC ） AS  RNK 
  FROM   @ EMP ） AS  A 
  WHERE  A.RNK = 1 

SalId EmpId TakenSal TakenDate AvailSal CompanyId
13 68 1000 02-Jul-2015 29000 1021
14 68 100 02-Jul-2015 28900 1021
15 69 1000 02-Jul-2015 23000 1021
16 82 1000 06-Jul-2015 19000 1024
17 82 1000 06-Jul-2015 18000 1024
18 83 1000 06-Jul-2015 19000 1024
19 83 1000 06-Jul-2015 18000 1024
20 82 1000 06-Jul-2015 17000 1024
21 82 1000 06-Jul-2015 16000 1024
22 82 1000 06-Jul-2015 15000 1024
23 82 1000 06-Jul-2015 14000 1024
24 94 1000 09-Jul-2015 19000 1014
25 94 1000 09-Jul-2015 18000 1014
26 94 1000 09-Jul-2015 17000 1014
27 94 1000 09-Jul-2015 18000 1014

How do I get 68,69,82,83,94 employees last records in sql?

解决方案

Order by SalId descending and then use the TOP keyword to get the topmost row.
This will be your last record!

select em.* from employees em join (
select max(SalId) as SalId,EmpId from employees
group by EmpId) as s on em.SalId = s.SalId and em.EmpId = s.EmpId

or

select * from [tbl_emps] where [alId] in( SELECT max(alId) FROM [tbl_emps] group by empid ) 

Try like below

SELECT * FROM
(SELECT EmpId,SalId,
DENSE_RANK() OVER (PARTITION BY SalId ORDER BY EmpId DESC) AS RNK
FROM @EMP ) AS A
WHERE A.RNK=1

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