mysql - 如何获取一周内两列的总计

Question

如何获取每位员工每周的总缺工时间。每月。我有一个 View ，其中包含每位员工每天的undertime 和overtime 列。但是我需要每周获取TotalUndertime。

|EmpID  |DayofWeek|DatePresent|Overtime |Undertime|
|3050001|Friday   |2016-04-01 |         |00:01:00 |
|3050001|Monday   |2016-04-04 |         |01:00:00 |
|3050001|Tuesday  |2016-04-05 |00:30:00 |         |
|3050001|Wednesday|2016-04-06 |00:30:00 |         |
|3050001|Thursday |2016-04-07 |00:05:00 |         |
|3050001|Friday   |2016-04-08 |00:05:00 |         |

如果员工在周一有加类，员工可以支付周二之后几天的加类费用 -星期五。或者，如果员工在周二有加类费，则该员工可以在周三至周五支付加类费。上表中的TotalUndertime = "00:01:00"`。

当涉及到使用日期和时间的 mysql 查询时，我只是一个新手。我应该使用函数还是过程？

我使用此代码来获取它，但它不起作用。

CREATE DEFINER =  `root`@`localhost` PROCEDURE  `getUndertime` ( IN  `varDatePresent` DATE, IN  `varEmpID` VARCHAR( 8 ) ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
BEGIN SELECT DAY( LAST_DAY( varDatePresent ) ) 
INTO @totaldays ;

SET @daycount =0;

WHILE(
@daycount < @totaldays
) DO SELECT Undertime
FROM view_dtr
WHERE EmpID LIKE  '%varEmpID%'
AND DatePresent = varDatePresent
INTO @undertime ;

SELECT Overtime
FROM view_dtr
WHERE EmpID LIKE  '%varEmpID%'
AND DATE_ADD( DatePresent, INTERVAL 1 
DAY ) 
INTO @overtime ;

SET @totalUndertime = @undertime - @overtime ;

SET @daycount = @daycount +1;

END WHILE;

SELECT @totalUndertime ;

END ;

任何建议都会对我有很大帮助。预先感谢您。

Answer 1

Group By 不能解决问题吗？

SELECT EMPID, Week(DatePresent) as 'WeekNumber',
SEC_TO_TIME(SUM(COALESCE(undertime,0)-COALESCE(overtime,0)) * 3600) as 'TotalUndertime' 
FROM table_name GROUP BY EmpID, Week(DatePresent)

用法:https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week

编辑:根据您对 TotalUndertime 的定义。作为旁注，我强烈建议在过滤 EMPID 时不要使用 LIKE。