php sql求和取值,php – 如何使用sql查询获取时间总和值？

我有一个ReportBuilder类用于生成文档(Excel).我想要的是选择员工和日期,然后计算该日期的总工作时间.这是我的php文件

class EmployeeSummaryTimesheet extends ReportBuilder{

public function getMainQuery(){

$query = "SELECT

(SELECT `employee_id` from Employees where id = at.employee) as 'Employee',

(SELECT concat(`first_name`,' ',`middle_name`,' ', `last_name`) from Employees where id = at.employee) as 'Employee',

TIMEDIFF(out_time, in_time) as 'Working Hours'

FROM Attendance at";

return $query;

}

public function getWhereQuery($request){

if(($request['employee'] != "NULL" && !empty($request['employee']))){

$query = "where employee = ? and in_time >= ? and out_time <= ? group by employee;";

$params = array(

$request['employee'],

$request['date_start']." 00:00:00",

$request['date_end']." 23:59:59",

);

}else{

$query = "where in_time >= ? and out_time <= ? group by employee;";

$params = array(

$request['date_start']." 00:00:00",

$request['date_end']." 23:59:59",

);

}

return array($query, $params);

}}

现在我无法得到那个特定日期的总和我第一天获得TIMEDIFF(out_time,in_time)值只是没有工作时间总和帮助我

编辑 – 这里我有两个表Employee和Attendance(id,employee(这是Employee表中的员工ID),in_time,out_time)

编辑：我将DATE_FORMAT(in_time,’％Y-％m-％d’)取消为’Date’,我不需要此列

最佳答案 您希望每位员工和日期有一条记录,并显示该员工当天的总工作时间.因此,按员工和日期加入和分组,并将工作时间相加.

select

e.employee_id,

concat(e.first_name, ' ', e.middle_name, ' ', e.last_name) as employee_name

date_format(at.in_time, '%Y-%m-%d') as 'Date',

sum(timediff(at.out_time, at.in_time)) as 'Working Hours'

from attendance at

join employee e on e.id = at.employee

where ...

group by e.employee_id, date_format(at.in_time, '%Y-%m-%d');