我有一张桌子
id | Loc_id | time_in | time_out | duration | prev
1 | Loc A | 2020-06-16 06:58:29 | 2020-06-16 07:05:45 | 7 | Null
2 | Loc B | 2020-06-16 07:07:20 | 2020-06-16 07:16:43 | 9 | Loc A
3 | Loc B | 2020-06-16 07:18:25 | 2020-06-16 07:23:28 | 5 | Loc B
4 | Loc C | 2020-06-16 07:25:11 | 2020-06-16 07:28:16 | 3 | Loc B
5 | Loc D | 2020-06-16 07:28:16 | 2020-06-16 07:33:01 | 4 | Loc C
6 | Loc D | 2020-06-16 07:34:44 | 2020-06-16 07:39:05 | 4 | Loc D
7 | Loc B | 2020-06-16 07:40:47 | 2020-06-16 07:44:16 | 3 | Loc D
如果下一行值与当前行值相同，我想要得到持续时间的和
id | Loc ID | duration
1 | Loc A | 7
2 | Loc B | 14
3 | Loc C | 3
4 | Loc D | 8
5 | Loc B | 3
对于MySQL8.0+，可以使用sum()窗口函数创建所需的组，然后进行聚合:
select t.loc_id, sum(t.duration) duration
from (
select *, sum(loc_id <> coalesce(prev, loc_id)) over (order by id) grp
from tablename
) t
group by t.grp, t.loc_id
或者如果还需要id列:
select t.grp + 1 id, t.loc_id, sum(t.duration) duration
from (
select *, sum(loc_id <> coalesce(prev, loc_id)) over (order by id) grp
from tablename
) t
group by t.grp, t.loc_id
请参阅演示。
结果:
> id | loc_id | duration
> -: | :----- | -------:
> 1 | Loc A | 7
> 2 | Loc B | 14
> 3 | Loc C | 3
> 4 | Loc D | 8
> 5 | Loc B | 3
