从连接表中检索最大(日期)值

Question

我需要一个非常基本的SQL连接概念的帮助，我似乎就是搞不懂。

我有一个employee表和一个position表。employee表如下所示：

EmpID   Name
1       Jane Jones
2       Bob Smith
3       Jim Adams

position表如下所示：

PosID   EmpID        Position   DateFilled
1       1            Sales      1/2/2012
2       2            HR         4/5/2013
3       2            Mgmnt      6/1/2014
4       2            Sr. Mgmnt  7/5/2015
5       3            IT Support 4/6/2014
6       3            IT Devel.  5/11/2015

如何获得以下输出：

EmpID   Name        Position    DateFilled
1       Jane Jones  Sales       1/2/2012
2       Bob Smith   Sr. Mgmnt   7/5/2015
3       Jim Adams   IT Devel.   5/11/2015

那么，换句话说，我如何进行连接，以便只从position表中获得具有最大DateFilled列的记录，以便与employee表中的相应记录进行连接？任何帮助都将不胜感激。

Answer 1

您可以使用ROW_NUMBER

SELECT e.EmpID, e.Name, p.Position, p.DateFilled
FROM employee e
LEFT JOIN (
   SELECT EmpID, Position, DateFilled,
          ROW_NUMBER() OVER (PARTITION BY EmpID 
                             ORDER BY DateFilled DESC) AS rn        
   FROM position
) p ON e.EmpID = p.EmpID AND p.rn = 1

Answer 2

你可以像这样使用MAX() KEEP ( DENSE_RANK [FIRST|LAST] ... )来完成：

SELECT e.EmpId,
       e.Name,
       p.position,
       p.datefilled
FROM   employee e
       INNER JOIN (
         SELECT EmpID,
                MAX( Position ) KEEP ( DENSE_RANK LAST ORDER BY DateFilled ) AS Position,
                MAX( DateFilled ) AS DateFilled
         FROM   position
         GROUP BY EmpID
       ) p
       ON ( e.EmpId = p.EmpID );

Answer 3

尝尝这个

 select temp.EmpID,(select position from Position where PosID =temp.PosID) position,DateFilled,Name from 
    (select EmpID,max(PosID) PosID,max(DateFilled) DateFilled 
     from position group by EmpID ) temp
    inner join employee  emp on emp.EmpID =temp.EmpID