如何基于关联条件获取员工对应周的最新Rate值？

要实现为EmployeeWeek表中每条记录匹配对应员工周数不大于当前周的最新薪资率（无匹配则为空），你可以用以下几种高效的SQL写法：

方法1：关联子查询（简洁通用） #

针对每条EmployeeWeek记录，通过子查询直接筛选出符合条件的最新薪资率，兼容绝大多数数据库（MySQL、PostgreSQL、SQL Server等，SQL Server需将LIMIT 1替换为TOP 1）：

SELECT 
    EW.employee,
    EW.week,
    (
        SELECT R.rate
        FROM Rates R
        WHERE R.employee = EW.employee
          AND R.week <= EW.week
        ORDER BY R.week DESC
        LIMIT 1
    ) AS rate
FROM EmployeeWeek EW;

方法2：窗口函数+关联查询 #

先对Rates表按员工分组、周数倒序排序，标记每条记录的排序序号，再关联EmployeeWeek筛选出符合条件的最新记录：

SELECT 
    EW.employee,
    EW.week,
    R.rate
FROM EmployeeWeek EW
LEFT JOIN (
    SELECT 
        employee,
        week,
        rate,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY week DESC) AS rn
    FROM Rates
) R ON R.employee = EW.employee 
   AND R.week <= EW.week 
   AND R.rn = 1;

方法3：LATERAL JOIN（适合特定数据库） #

如果使用PostgreSQL、SQL Server或Oracle 12c及以上版本，可用LATERAL JOIN实现更高效的关联查询，尤其适合大数据量场景：

SELECT 
    EW.employee,
    EW.week,
    R.rate
FROM EmployeeWeek EW
LEFT JOIN LATERAL (
    SELECT rate
    FROM Rates R
    WHERE R.employee = EW.employee
      AND R.week <= EW.week
    ORDER BY R.week DESC
    LIMIT 1
) R ON true;

原查询问题说明 #

你之前的LEFT JOIN语句会返回所有满足EW.employee = R.employee AND EW.week >= R.week的记录，因此每条EmployeeWeek记录会对应多条薪资记录。而我们需要的是其中周数最大的那条，上述方法都通过筛选排序后的第一条记录，解决了多记录匹配的问题。

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