谢谢楼上kisscolo 的关注。可能是我没有把问题描述清楚。
以后有问题一定向你请教。 谢谢。
以下是完整的SQL语句,供遇到同样问题的朋友参考
SELECT e.EmployeeID AS 工號,
e.ChineseName AS 姓名,
e.EnglishName AS 英文名,
e.Position AS 职位,
a.AOT AS [加班(小时)],
a.ALV AS [请假(小时)],
d.DWKD AS 应上班天数,
ROUND((应上班天数*9 + IIF(ISNULL([加班(小时)]),0,[加班(小时)]) - IIF(ISNULL([请假(小时)]),0,[请假(小时)]))/9,2) AS 实际上班天数,
IIF(ISNULL(o.OIM),0,o.OIM) + IIF(ISNULL(o.OCMS),0,o.OCMS) AS [总产量(款)],
ROUND([总产量(款)]/实际上班天数,1) AS [日均产量(款)],
r.RJ AS [返工数量(款)],
ROUND([返工数量(款)]/[总产量(款)]*100,2) AS [返工率(%)]
FROM (((Employee e
LEFT JOIN
(SELECT EmployeeNo,SUM(OvertimeDuration) AS AOT,SUM(LeaveDuration) AS ALV
FROM Attendance
WHERE DurationFrom >= 'DATEFROM' AND DurationTo <= 'DATETO'
GROUP BY EmployeeNo) a
ON e.EmployeeID = a.EmployeeNo)
LEFT JOIN
(SELECT m.EmployeeID AS DEMPID, COUNT(w.WorkDate) AS DWKD
FROM Employee m, Demand w
WHERE w.WorkDate BETWEEN IIF(m.StartDate > 'DATEFROM', m.StartDate, 'DATEFROM') AND
(IIF(m.LeaveDate IS NULL OR m.LeaveDate = '', 'DATETO', IIF(m.LeaveDate < 'DATETO', m.LeaveDate,'DATETO' )))
AND (w.Remark IS NULL OR w.Remark ='')
GROUP BY m.EmployeeID) d
ON d.DEMPID = e.EmployeeID)
LEFT JOIN
(SELECT BrsID,SUM(IndividualMarker) AS OIM,SUM(CombineMarkerByStyle) AS OCMS
FROM Output
WHERE WorkDate BETWEEN 'DATEFROM' AND 'DATETO'
GROUP BY BrsID) o
ON e.BrsUserName = o.BrsID)
LEFT JOIN
(SELECT BrsID,SUM(Quantity) AS RJ
FROM Rejection
WHERE DurationFrom >= 'DATEFROM' AND DurationTo <= 'DATETO'
GROUP BY BrsID) r
ON e.BrsUserName = r.BrsID
ORDER BY ORDERNUMBER ORDERTYPE