PostgreSQL中CTE无法关联主查询表的解决方案探讨

原查询的问题在于CTE无法引用主查询中后续出现的表（ER和WS），PostgreSQL的CTE是独立执行的，不能依赖主查询的上下文。要实现针对每个员工的每条排班记录，获取符合effectiveDate >= dateSoS条件的第一条empComp记录，可以使用LATERAL JOIN——它允许子查询访问主查询中已经连接的表的字段。

修改后的查询语句如下：

SELECT 
    ER.lastName, 
    ER.firstName, 
    ER.employeeNumber, 
    EC.hourlyBonus, 
    EC.shiftBonus,
    WS.dateSoS, 
    EXTRACT(EPOCH FROM (WS.dateEoS + WS.timeEoS - WS.dateSoS - WS.timeSoS)) / 60 AS shiftDuration, 
    WS.wageType,
    H.holidate 
FROM workSchedules WS 
INNER JOIN schedXEmp SXE ON SXE.schedId = WS.recId 
INNER JOIN empRec ER 
    ON ER.selfManagerId = 1 
    AND ER.employeeNumber = SXE.employeeNumber 
-- 使用LATERAL LEFT JOIN，针对每条ER+WS组合获取首条符合条件的empComp记录
LEFT JOIN LATERAL (
    SELECT hourlyBonus, shiftBonus
    FROM empComp
    WHERE selfmanagerid = 1 
      AND employeeNumber = ER.employeeNumber 
      AND effectiveDate >= WS.dateSoS
    ORDER BY effectiveDate ASC -- 确保取最早生效的记录
    FETCH FIRST ROW ONLY
) EC ON true
LEFT JOIN lkupHolidays H 
    ON H.holidate = WS.dateSoS 
    AND H.deactivatedTs > H.holidate 
WHERE WS.dateSoS BETWEEN '2020-04-15' AND '2020-10-15' 
  AND WS.deactivatedTs > CURRENT_TIMESTAMP 
  AND WS.selfmanagerid = 1 
ORDER BY ER.lastName, ER.firstName, WS.wageType, WS.dateSoS, WS.timeSoS;

关键说明： #

• LATERAL JOIN：让子查询能够访问主查询中已经连接的ER（员工信息）和WS（排班信息）表的字段，实现逐行关联查询。
• 子查询排序：添加ORDER BY effectiveDate ASC确保获取的是最早生效且符合effectiveDate >= WS.dateSoS的记录，如果需要最新生效的记录可以改为DESC。
• 修正重复字段：原查询中SELECT部分重复写了hourlyBonus, shiftBonus和EC.hourlyBonus, EC.shiftBonus，已删除重复项，仅保留EC.前缀的字段。
• LEFT JOIN vs INNER JOIN：如果每个员工的每条排班记录都必须有对应的empComp记录，可以将LEFT JOIN LATERAL改为INNER JOIN LATERAL。

另外，如果empComp表有大量数据，建议给(selfmanagerid, employeeNumber, effectiveDate)创建复合索引，提升子查询的查询效率。

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