我一直在使用CHAT GTP来创建我需要的查询。我们一起得到了：

WITH employeeCompensation AS 
     (SELECT hourlyBonus, shiftBonus
      FROM empComp
      WHERE selfmanagerid = 1 AND
            employeeNumber = ER.employeeNumber AND
            effectiveDate >= WS.dateSoS
      FETCH FIRST ROW ONLY)        
SELECT ER.lastName, ER.firstName, ER.employeeNumber, hourlyBonus, shiftBonus
       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 
        LEFT JOIN lkupHolidays H ON (H.holidate = WS.dateSoS AND 
                                     H.deactivatedTs > H.holidate) 
WHERE WS.dateSoS BETWEEN '2020-4-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
;

字符串
当然，这不起作用，因为PostgreSQL不喜欢在CTE中引用ER和WS。它希望我将它们移动到靠近底部的INNER JOIN中。但是这样做不会在我的CTE中获得相同的记录，因为它会获得任何雇员和日期的第一条记录，然后尝试加入那一条记录。
有人有什么想法吗？