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 time 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)如何在 SQL Server 中将多行文本连接成单个文本字符串？ b)计算mysql中两个时间戳的时间差

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

标签： mysqlsql

Answer 1

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

;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)