第二个的方法:select a.ryid,a.ddtime as '进入时间',b.ddtime as '出口时间',datediff(mi,a.ddtime,b.ddtime) as '停留时间(单位:分钟)' from table1 a left join table1 b
on a.ryid=b.ryid
where a.address='a区' and b.address='c区'
on a.ryid=b.ryid
where a.address='a区' and b.address='c区'
from
(
select *,总停留时间=datediff(mi,ddtime,(select top 1 ddtime from table1 c where a.ryid=c.ryid and a.ddtime<c.ddtime order by ddtime desc)) from table1 a where not exists(select 1 from table1 b where a.ryid=b.ryid and a.ddtime>b.ddtime) and address='a区'
)m left join
(
select *,在最后区域停留时间=datediff(mi,ddtime,(select ddtime from table1 c where a.ryid=c.ryid and a.ddtime<c.ddtime)) from table1 a where not exists(select 1 from table1 b where a.ryid=b.ryid and a.ddtime<b.ddtime and b.address<>'c区') and address<>'a区'
and address<>'c区'
)n on m.ryid=n.ryid