多表关联时如何获取同eight_id的最新tblSign记录？

现有SQL查询语句如下：

SELECT tblSign.sigdate,tblSign.sigtime,tblSign.sigact,tblSign.esignature,tblEmpl.fname,tblEmpl.lname,tblEmpl.location, tblEmpl.estatus,tblLocs.unit,tblLocs.descript,TblLocs.addr1,tblLocs.city,tblLocs.state, tblLocs.zip
FROM tblEmpl
LEFT JOIN tblSign
   ON tblSign.eight_id = tblEmpl.eight_id
      AND tblSign.formid = '9648'
      AND tblSign.sigact <> 'O'
      AND tblSign.sigdate >= '2022-11-01'
LEFT JOIN tblLocs
    ON tblEmpl.location = tblLocs.location
WHERE tblEmpl.estatus = 'A'
    AND tblEmpl.location = '013'
ORDER BY
     tblSign.sigdate ASC;

tblSign表中存在多条相同eight_id的记录，希望关联多表时，仅获取tblSign中每个eight_id对应的最新记录。

当前查询返回的数据：

期望得到的结果：

要实现每个eight_id只取tblSign中的最新记录，可使用**窗口函数ROW_NUMBER()**对每个eight_id的记录按时间排序，筛选出排序为1的最新记录。修改后的SQL如下：

SELECT s.sigdate, s.sigtime, s.sigact, s.esignature, e.fname, e.lname, e.location, e.estatus, l.unit, l.descript, l.addr1, l.city, l.state, l.zip
FROM tblEmpl e
LEFT JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY eight_id ORDER BY sigdate DESC, sigtime DESC) AS rn
    FROM tblSign
    WHERE formid = '9648'
      AND sigact <> 'O'
      AND sigdate >= '2022-11-01'
) s ON s.eight_id = e.eight_id AND s.rn = 1
LEFT JOIN tblLocs l ON e.location = l.location
WHERE e.estatus = 'A'
  AND e.location = '013'
ORDER BY s.sigdate ASC;

关键说明： #

• 子查询中用PARTITION BY eight_id将记录按eight_id分组，ORDER BY sigdate DESC, sigtime DESC确保每组内按日期、时间倒序排列，最新的记录排在最前面。
• ROW_NUMBER()为每组内的记录分配序号，rn=1即对应每组的最新记录。
• 原查询中tblSign的筛选条件移至子查询内，避免关联后筛选导致数据丢失。

内容的提问来源于stack exchange，提问作者TimmyTurn123