mysql - 从具有时间戳和访客 ID 的 SQL 表生成访客 InTime 访客 OutTime 和访问持续时间(时间差)的 SQL 查询

Question

MySQL 服务器上的访客数据库如下所示id 是主键整数类型，firstname 和 lastname 是文本类型，访客 id 是整数类型

id  firstname       lastname    visitorid
1   userfirstname1      userlastname1   1
2   userfirstname2      userlastname2   2
3   userfirstname3      userlastname3   3
4   userfirstname4      userlastname4   4
5   userfirstname5      userlastname5   5

访问数据库MySQL服务器如下所示 id 是主键整数类型，时间是 DateTime 类型，visitorid_id 是访问者表的外键 (visitor->visitorid ) id时间visitorid_id

id  scantime                visitorid_id    
433 2019-12-25 09:00:00.000000      1   
434 2019-12-25 18:00:00.000000      1   
435 2019-12-26 09:00:00.000000      1   
436 2019-12-26 18:00:00.000000      1   
437 2019-12-27 09:00:00.000000      1   
438 2019-12-27 18:00:00.000000      1   
439 2019-12-28 09:00:00.000000      1   
440 2019-12-28 18:00:00.000000      1   
441 2019-12-29 09:00:00.000000      1   
442 2019-12-29 18:00:00.000000      1   
443 2019-12-25 10:00:00.000000      2   
444 2019-12-25 17:00:00.000000      2   
445 2019-12-25 09:30:00.000000      3   
446 2019-12-25 17:30:00.000000      3   
447 2019-12-25 08:00:00.000000      5   
448 2019-12-25 18:00:00.000000      5   
449 2019-12-25 11:30:00.000000      4   
450 2019-12-25 17:30:00.000000      4

我需要从上面的表格生成显示以下数据的报告

日期、访客名字、访客姓氏、第一次扫描时间、第二次扫描时间、第一次扫描与第二次扫描时间之间的时间差(HH:MM:SS 格式)。

我使用了以下链接，但无法获得生成所需报告的正确 SQL 查询a) How to concatenate text from multiple rows into a single text string in SQL server?b) Calculate the time difference between two timestamps in mysql

什么是优化查询来生成大号所需的报告。记录。

Answer 1

这个解决方案 San 将为您提供我的 POV 所需的内容。我的理解是你想要前两次访问。对于差异，我以秒来表示。您可以转换为 HH:MM:SS。我没有“日期”列，因为我不知道您想要什么。

;with firstvisit as
(
select visitorid_id, min(scantime) as firstscan from visits a group by visitorid_id
)
select a.visitorid_id, firstname, lastname, a.firstscan, b.scantime as secundscan, TIMESTAMPDIFF(SECOND, b.scantime, a.firstscan) from firstvisit a 
inner join visitors v on v.visitorid = a.visitorid_id
inner join visits b on  a.visitorid_id = b.visitorid_id and b.scantime > a.firstscan 
where not exists(select 1 from visits c where a.visitorid_id = c.visitorid_id and c.scantime > a.firstscan and c.scantime < b.scantime)

不使用 CTE 的查询:

select a.visitorid_id, firstname, lastname, a.firstscan, b.scantime as secundscan, TIMESTAMPDIFF(SECOND, b.scantime, a.firstscan) 
from (select visitorid_id, min(scantime) as firstscan from visits a group by visitorid_id) a 
inner join visitors v on v.visitorid = a.visitorid_id
inner join visits b on  a.visitorid_id = b.visitorid_id and b.scantime > a.firstscan 
where not exists(select 1 from visits c where a.visitorid_id = c.visitorid_id and c.scantime > a.firstscan and c.scantime < b.scantime)