如何在sql中获取员工的最后记录？

本文介绍了如何在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