我有一个员工文件和一个地址文件(通过seq #)，我正在尝试获取员工的当前地址

Question

员工文件将包含Clock#、Name等地址文件具有CLock#、seq、add1、add2等

我需要获得所有在职员工的当前(最高序列号(地址

我在下面尝试了一下，但只得到了一条具有最高序列的线路

SELECT HRPEMPM.EMEMPN, HRPEMPM.EMALPH, HRPE_ADD.EAADD1,         
HRPE_ADD.EACITY, HRPE_ADD.EAPOST, HRPE_ADD.EASEQ 
FROM hrpempm, hrpe_add 
WHERE easeq = (select max(easeq) from hrpe_add ) and   
HRPE_ADD.EAEMPN = emempn; 
Example 
Clock   Name           Status     Seniority 
1234    Mickey Mouse      A         2012/01/01
4567    Minnie Mouse      A         2015/06/01
Address file
Clock   Seq  Address 1            City        Prov
1234      1  124 King st.        Hamilton     Ont.
1234      2  525 Corman Ave.     Burlington   Ont.
1234      3  878 West 5th        Dundas       Ont.
4567      1  10 Mountain Ave.    Hamilton     Ont.
4567      2  777 Airport Rd      Mount Hope   Ont.  
Result expected
1234   Mickey Mouse    878 West 5th       Dundas       Ont.
4567   Minnie Mouse    777 Airport Rd     Mount Hope   Ont.      

Answer 1

我会使用这种方法：首先选择最新记录，然后加入它们：

;with a as(
select *
, rnk = row_number() over (partition by EAEMPN order by easeq desc)
from hrpe_add
)
SELECT HRPEMPM.EMEMPN, HRPEMPM.EMALPH, HRPE_ADD.EAADD1,         
HRPE_ADD.EACITY, HRPE_ADD.EAPOST, HRPE_ADD.EASEQ 
FROM hrpempm
join a on a.rnk = 1 and
a.EAEMPN = hrpempm.emempn;

Answer 2

看起来你已经接近了。我会尝试以下方法：

SELECT a.EMEMPN, a.EMALPH, b.EAADD1,         
b.EACITY, b.EAPOST, b.EASEQ 
FROM hrpempm a
join hrpe_add b on a.EMEMPN = a.EAEMPN
WHERE b.easeq = (select max(easeq) from hrpe_add
where eaempm = a.emempn)

您需要员工的最大序列号，而不是在表中获取最大序列号。