从同一表中获取不同值的SQL查询

Question

假设我有这样一个表:employeetable:

tbody> <<tr>2124

empid	joindate	location	HRupdatedate	Changes
1	2002-01-05	德里	2021-03-01	新
	2009-09-09	孟买	2021-03-05	新
	2010-06-27	海德拉巴	2021-03-03	转移
	2015-11-02	德里	2021-03-06	转移
3	2020-01-01	孟买	2021-03-06	新
	2007-07-30	德里	2021-03-04	新

您可以通过使用WHERE子句轻松实现这一点。

select * from  
employeetable et1
where 
joindate = (
select max(joindate)
from employeetable et2 
where et1.empid = et2.empid
) 
and 
empid in (1,2, 4);

或者得到更精确的结果

select * from  
employeetable et1
where 
joindate = (
select max(joindate)
from employeetable et2 
where et1.empid = et2.empid
) 
and 
HRupdatedate = (
select max(HRupdatedate)
from employeetable et2 where et1.empid = et2.empid
)
and 
empid in (1,2, 4);

您可以使用top with ties

select top(1) with ties *
from employeetable
where empid in (1,2,4)
order by row_number() over(partition by empid order by joindate desc)