mysql计算时间总长_计算mysql中所有先前条目的总时间之和

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table

(play_id SERIAL PRIMARY KEY

,hr_temp_id INT NOT NULL

,min_id TIME NOT NULL

);

INSERT INTO my_table VALUES

(29,43,'00:00:10'),

(30,43,'00:02:00'),

(31,43,'00:00:10'),

(32,44,'00:00:10'),

(33,44,'00:03:15');

SELECT x.*

, SEC_TO_TIME(SUM(TIME_TO_SEC(y.min_id))) running

FROM my_table x

JOIN my_table y

ON y.hr_temp_id = x.hr_temp_id

AND y.play_id <= x.play_id

GROUP

BY x.play_id;

+---------+------------+----------+----------+

| play_id | hr_temp_id | min_id | running |

+---------+------------+----------+----------+

| 29 | 43 | 00:00:10 | 00:00:10 |

| 30 | 43 | 00:02:00 | 00:02:10 |

| 31 | 43 | 00:00:10 | 00:02:20 |

| 32 | 44 | 00:00:10 | 00:00:10 |

| 33 | 44 | 00:03:15 | 00:03:25 |

+---------+------------+----------+----------+

…要么…

SELECT x.*

, SEC_TO_TIME(CASE WHEN @prev = hr_temp_id THEN @i:= @i+TIME_TO_SEC(min_id) ELSE @i:=TIME_TO_SEC(min_id) END) running

, @prev := hr_temp_id prev

FROM my_table x

, (SELECT @prev:=null,@i:=0) vars

ORDER

BY hr_temp_id

, play_id;