如何提取最新的记录

Question

我有两个表是这样的： -

表A

ITEM LOCATION CODE 
A  WHS   QW123 
B  WHS   QW124 
C  WHS   QW125 

表B

ATR_NO   ITEM CODE ATT_ID ATT_VALUE 
20110101123310 A QW123 SIZE 24 
20110101123310 A QW123 GRADE 2 
20110101123351 B QW124 SIZE 20 
20110101123351 B QW124 GRADE 3 
20110101124042 C QW125 SIZE 26 
20110101124042 C QW125 GRADE 4 
20110101131210 A QW123 SIZE 26 
20110101131210 A QW123 GRADE 1 
20110101144542 C QW125 SIZE 27 
20110101144542 C QW125 GRADE 1 

使用SQL shold这个样子

的预计业绩： -

结果

ITEM LOCATION CODE SIZE GRADE 
A  WHS   QW123 26 1 
B  WHS   QW124 20 3 
C  WHS   QW125 27 1 

我该如何做到这一点？

Answer 1

获取最新ATR_NO每个项目，携手与表B两次获得规模和档次：

select 
    a.ITEM, a.LOCATION, a.CODE, SIZE = b1.ATT_VALUE, GRADE = b2.ATT_VALUE 
from 
    [TABLE A] a 
    inner join (select max(ATR_NO) from [TABLE B] group by ITEM) i on i.ITEM = a.ITEM 
    inner join [TABLE B] b1 on b1.ATR_NO = i.ATR_NO and b1.ATT_ID = 'SIZE' 
    inner join [TABLE B] b2 on b2.ATR_NO = i.ATR_NO and b2.ATT_ID = 'GRADE' 
order by 
    a.ITEM