我有一个MYSQL表的任务,其中每个任务有一个日期,开始时间,结束时间和user_id。我想计算特定日期的总小时数。
表结构
CREATE TABLE tasks
(`id` int,`user_id` int, `title` varchar(30), `task_date` datetime, `start` time, `end` time)
;
INSERT INTO tasks
(`id`,`user_id`, `title`,`task_date`, `start`, `end`)
VALUES
(1,10, 'Task one','2013-04-02', '02:00:00', '04:00:00'),
(2,10, 'Task two','2013-04-02', '03:00:00', '06:00:00'),
(3,10, 'Task three.','2013-04-02','06:00:00', '07:00:00');
MYSQL查询select TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF( end, start)))), "%h:%i") AS diff
FROM tasks
where task_date="2013-04-02"
我得到的结果是"06:00"小时,这很好,但我想排除重叠的时间。在这个例子中,我给出的结果应该是"05:00"小时,当2个记录中的3-4之间的小时被排除,因为这个小时已经存在于2-4之间的第一个记录中。
- 第一条记录2=>4 = 2小时
- 第二条记录3=>6 = 3小时3-1小时=2(其中1小时为第一条记录与第二条记录的重叠时间)
- 3 6 => 7 = 1
共5小时
我希望我把问题讲清楚了。 http://sqlfiddle.com/!2/05dd8/2这里有一个使用变量的想法(在其他数据库中,cte和窗口函数会使这更容易)。这个想法是首先列出所有的时间——开始和结束。然后,记录启动和停止的累计次数。
当累计数大于0时,则包括与前一次的差值。如果等于0,那么它是一个新时间段的开始,因此没有添加任何内容。
下面是查询的一个示例,它通过不跟踪user_id
中的变化来简化数据:
select user_id, TIME_FORMAT(SEC_TO_TIME(sum(secs)), '%h:%i')
from (select t.*,
@time := if(@sum = 0, 0, TIME_TO_SEC(TIMEDIFF(start, @prevtime))) as secs,
@prevtime := start,
@sum := @sum + isstart
from ((select user_id, start, 1 as isstart
from tasks t
) union all
(select user_id, end, -1
from tasks t
)
) t cross join
(select @sum := 0, @time := 0, @prevtime := 0) vars
order by 1, 2
) t
group by user_id;
这里是一个SQL Fiddle显示它的工作