[求助] 如何用 SQL（或者其他方式）实现这个要求（内详）

[2bin]

数据格式如下： https://sm.ms/image/42rhuonQbDZYT1S

需求：汇总不同楼层的逗留时长。

想了下没有合适的办法，各位 v 友有没有好的办法，请赐教

[su2018]

不知道符不符合你要求

SELECT t.floor, SUM(t.wait_time)wait_time,t.floor_index
FROM (
SELECT visit_date,FLOOR,TIME_TO_SEC(TIMEDIFF(t_time,@startTime)) wait_time,@startTime:=t_time,IF(a.floor=@floor,@index,@index:=@index+1),@index floor_index,@floor := a.floor

FROM test1 a,(SELECT @startTime := '') AS b,(SELECT @floor :=0) AS c,(SELECT @index :=0) AS d WHERE 1

ORDER BY t_time

) t GROUP BY t.floor_index

[x66]

row_number 根据 t_time 排序，然后自连接 a.row_number = b.row_number +1 ,然后 group by floor,求差再求和就好了

[zhuanggu]

select floor
,sum(next_time-t_time) as ts
from
(
select floor
,t_time
,lead(t_time,1) over(partition by floor order by t_time ) as next_time
from tablename
) t
group by floor;