我试图在过去7天内通过属于同一项目的用户在表中总结所有时间(timers.date_time at date_time格式(,并向用户显示没有做任何工作,例如00:00:00格式。
计时器表
Name Type
timer_id int(11)
user_id int(5)
user_name varchar(250)
Proj_id Int(10)
Proj_name varchar(250
workingOn text
date_time datetime
登录表
Name Type
id_login int(11)
name_login varchar(45)
username varchar(100)
password varchar(18)
useremail varchar(45)
tel_number varchar(12)
type_login int(5)
我的最后一个走是如下,
SELECT *
FROM
(SELECT timers.user_name,timers.workingOn,Date_Format(timers.date_time,
'%d/%m/%Y') AS DDAte,Time_Format(timers.date_time, '%H:%i:%s') AS Duration
FROM timers
WHERE DATE(timers.date_time) > (NOW() - INTERVAL 7 DAY) AND
Proj_name= 'Project 1'
ORDER BY timer_id DESC) AS x
GROUP BY user_name
以上查询不总结计时器。并且缺少在过去7天内没有工作的用户(作为用户名和00:00:00(
我一直在尝试过去的4天,没有运气,任何帮助将不胜感激。
您可以使用time_to_sec将时间转换为秒和sum(sec_to_time(获取valiala time结果
在此示例组中,X.user_name,X.Workingon,X.Ddate
SELECT x.user_name, x.workingOn, x.DDAte, SEC_TO_TIME(sum( x.duration))
FROM
( SELECT timers.user_name,timers.workingOn,Date_Format(timers.date_time,
'%d/%m/%Y') AS DDAte, TIME_TO_SEC( timers.date_time ) AS Duration
FROM timers
WHERE DATE(timers.date_time) > (NOW() - INTERVAL 7 DAY) AND
Proj_name= 'Project 1'
ORDER BY timer_id DESC) AS x
GROUP BY user_name, x.workingOn, x.DDAte
如果我假设"日期/时间"确实是"日期/持续时间",那么这是一种方法:
SELECT t.user_name, t.workingOn,
COALESCE(SEC_TO_TIME(seconds), '0:00:00')
FROM (SELECT t.user_name, t.workingOn,
SUM(CASE WHEN t.date_time >= curdate() -
interval 7 day)
THEN time_to_sec(time(date_time))
END) as seconds
FROM timers t
WHERE Proj_name = 'Project 1'
GROUP BY t.user_name, t.workingOn
) t
您实际上可以在没有子查询的情况下执行此操作,但是我认为分解逻辑可以更简单地遵循。