相邻数据拼接

select t.person_id,s.name,t.outtime,t.intime,s.id_card,s.ecard_no,s.tno,s.org,m.pass_name as outpass, n.pass_name as inpass
from
(
SELECT
– a.id AS aid,
– b.id AS bid,
a.person_id,
a.through_time AS outtime,
b.through_time AS intime,a.pass_id as outpass,b.pass_id as inpass
FROM
(
SELECT
( @rownum := @rownum + 1 ) AS rownum,
a.through_time,
– a.id,
a.person_id,
a.through_direction ,
a.pass_id
FROM
access_through_record a,
( SELECT @rownum := 0 ) r
WHERE
through_time >‘2020-06-01’ and through_time < ‘2020-07-01’
– 查询条件时间范围加这里
order by person_id
) a
LEFT JOIN (
SELECT
( @INDEX := @INDEX + 1 ) AS rownum,
a.through_time,
– a.id,
a.person_id,
a.through_direction,
a.pass_id
FROM
access_through_record a,
( SELECT @INDEX := 0 ) r
WHERE
through_time >‘2020-06-01’ and through_time < ‘2020-07-01’
– 查询条件时间范围加这里
order by person_id
) b ON a.person_id = b.person_id
WHERE
a.rownum = b.rownum - 1 and a.through_direction =2 and b.through_direction =1
and TIMESTAMPDIFF(MINUTE,b.through_time, a.through_time) >=20
) t left join access_person_info s on t.person_id = s.id left join dm_hk_camera_mapping m on t.outpass = m.pass_id
left join dm_hk_camera_mapping n on t.inpass = n.pass_id