在 Oracle 中使用 count 和 sum 连接三个表

Question

我有三个表，并尝试将这些表与count(working_days(和sum(Overtime(函数连接起来，但加班显示不正确，我正在尝试查询。

SELECT E.EMP_CODE,E.EMP_NAME,
COALESCE(SUM(O.OVERTIME),0) AS OVERTIME,COALESCE(COUNT(C.EMP_ATT),0) WORKED_DAYS
FROM EMP E
LEFT JOIN OVERTIME   O
ON E.EMP_CODE = O.EMP_CODE
LEFT JOIN ATT_REG C
ON E.EMP_CODE=C.EMP_CODE
GROUP BY E.EMP_CODE,E.EMP_NAME 
ORDER BY EMP_CODE

Answer 1

是的，您将获得员工的加班记录，因为联接中缺少轮班日期(或表中的类似日期(。

SELECT E.EMP_CODE,E.EMP_NAME,
COALESCE(SUM(O.OVERTIME),0) AS OVERTIME,
COALESCE(COUNT(C.EMP_ATT),0) WORKED_DAYS
FROM EMP E
LEFT JOIN ATT_REG C
ON E.EMP_CODE=C.EMP_CODE
LEFT JOIN OVERTIME   O
ON E.EMP_CODE = O.EMP_CODE 
AND C.SHIFTDATE = O.SHIFDATE -- YOU NEED SOMETHING LIKE THIS
GROUP BY E.EMP_CODE,E.EMP_NAME 
ORDER BY EMP_CODE