我想按EmployeeID,PayDateStart,JobDate和JobTime对运行总计列进行分类,我的代码如下:
SELECT EmployeeID,
PayDateStart,
JobDate,
JobTime,
JobNo,
NetHrs,
(
SELECT SUM(NetHrs)
FROM test2 t2
WHERE t1.EmployeeID = t2.EmployeeID
AND t1.PayDateStart >= t2.PayDateStart
AND t1.PayDateStart <= t2.JobDate
AND t1.JobDate >= t2.JobDate
) AS CummNetHrs
FROM test2 t1
ORDER BY EmployeeID, PayDateStart, JobDate, JobTime;
它给我这样的输出:
EmployeeID PayDateStart JobDate JobTime JobNo NetHrs CummNetHrs
666 06/03/16 06/04/16 13:00:00 6651 9.44 9.44
666 06/03/16 06/05/16 09:00:00 6653 8.18 17.62
666 06/03/16 06/07/16 09:00:00 6654 9.97 27.59
666 06/03/16 06/09/16 09:00:00 6661 9.43 37.02
666 06/10/16 06/11/16 09:00:00 6662 12.69 18.84
666 06/10/16 06/11/16 10:00:00 6663 6.15 18.84
666 06/10/16 06/12/16 08:00:00 6664 5.83 24.67
666 06/10/16 06/14/16 09:00:00 6665 7.27 31.94
666 06/10/16 06/15/16 09:00:00 6666 8.48 40.42
444 5/27/2016 5/27/2016 9:00:00 4441 9.33 9.33
444 5/27/2016 5/28/2016 8:30:00 4442 5.67 15
444 5/27/2016 5/30/2016 9:00:00 4443 9.67 24.67
444 5/27/2016 5/31/2016 9:00:00 4444 12.67 37.34
但是对于在同一天但多次工作的员工,运行总和代码无法计算-即使我尝试使用“ t1.JobTime> = t2.JobTime”,它也显示相同的累积值:
EmployeeID PayDateStart JobDate JobTime JobNo NetHrs CummNetHrs
666 6/10/2016 6/11/2016 9:00:00 6665 12.69 18.84
666 6/10/2016 6/11/2016 10:00:00 6664 6.15 18.84
任何建议将不胜感激!
最佳答案
请尝试以下...
SELECT EmployeeID,
PayDateStart,
JobDate,
JobTime,
JobNo,
NetHrs,
(
SELECT SUM( NetHrs )
FROM test2 t2
WHERE t1.EmployeeID = t2.EmployeeID
AND t1.PayDateStart >= t2.PayDateStart
AND t1.PayDateStart <= t2.JobDate
AND ( t1.JobDate + INTERVAL TIME_TO_SEC( t1.JobTime ) SECOND ) >= ( t2.JobDate + INTERVAL TIME_TO_SEC( t2.JobTime ) SECOND )
) AS CummNetHrs
FROM test2 t1
ORDER BY EmployeeID,
PayDateStart,
JobDate,
JobTime;
为了解决这个问题,我寻求了一种将
date
和time
字段加在一起的方法。我在Add DATE and TIME fields to get DATETIME field in MySQL找到了一个解决方案,并根据该解决方案修改了您的代码。如果您有任何问题或意见,请随时发表评论。