在MySQL中检查表中的连续记录
在MySQL中检查表中的连续记录
提问于 2019-04-11 07:28:17
我必须为我的营销团队拿出一份关于我们的销售人员所做的实地工作的报告，每个销售人员都有check_in时间和check_out时间来知道他在一家商店花了多少钱。类似地，我需要比较checkout_time和check_time，并得到这两个记录之间的时间差。
这是我的样品表：
S.No User_ID Name Check_in_Time Check_out_Time
1 123 XYZ 2019-04-11 09:00:00 2019-04-11 09:15:00
2 123 XYZ 2019-04-11 09:20:00 2019-04-11 09:30:00
3 123 XYZ 2019-04-11 10:00:00 2019-04-11 10:15:00
4 123 XYZ 2019-04-11 11:00:00 2019-04-11 11:30:00
5 567 ZZZ 2019-04-11 10:00:00 2019-04-11 10:30:00
6 567 ZZZ 2019-04-11 11:00:00 2019-04-11 11:30:00现在，我需要将第1 Check_out_Time和第2 Check_in_Time与时差进行比较，就像wise一样，我需要对给定日期的每个用户的所有check_out和check_in时间差进行比较。
请帮助编写这方面的MySQL查询。
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-04-11 07:49:53
您可以在MySQL早期8.0中使用以下解决方案(没有ROW_NUMBER支持)：
SELECT
t1.User_Id,
t1.Name,
t1.check_out_time,
t2.Name,
t2.Check_in_time,
TIMEDIFF(t2.Check_in_time, t1.check_out_time) AS diff
FROM (
SELECT *, IF(@prev <> User_ID, @rn:=0, @rn), @prev:=User_ID, @rn:=@rn+1 AS rn
FROM table_name JOIN (SELECT @prev:='') init_prev JOIN (SELECT @rn:=0) init_rn
ORDER BY User_ID, Check_in_Time, Check_out_Time
)t1 INNER JOIN (
SELECT *, IF(@prev <> User_ID, @rn:=0, @rn), @prev:=User_ID, @rn:=@rn+1 AS rn
FROM table_name JOIN (SELECT @prev:='') init_prev JOIN (SELECT @rn:=0) init_rn
ORDER BY User_ID, Check_in_Time, Check_out_Time
)t2 ON t1.User_ID = t2.User_ID AND t1.rn + 1 = t2.rn
WHERE DATE(t2.Check_in_time) = '2019-04-11' AND DATE(t1.check_out_time) = '2019-04-11'从MySQL 8.0开始，您可以使用ROW_NUMBER使用以下解决方案
SELECT
t1.User_Id,
t1.Name,
t1.check_out_time,
t2.Name,
t2.Check_in_time,
TIMEDIFF(t2.Check_in_time, t1.check_out_time) AS diff
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY User_ID, Check_in_Time, Check_out_Time) rn
FROM table_name
)t1 INNER JOIN (
SELECT *, ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY User_ID, Check_in_Time, Check_out_Time) rn
FROM table_name
)t2 ON t1.User_ID = t2.User_ID AND t1.rn + 1 = t2.rn
WHERE DATE(t2.Check_in_time) = '2019-04-11' AND DATE(t1.check_out_time) = '2019-04-11'Stack Overflow用户
发布于 2019-04-11 08:13:34
如果您没有使用MySQLVersion8.0，那么下面是解决方案，
SELECT
t1.User_Id,
t1.Name,
t1.check_out_time,
t2.Name,
t2.Check_in_time,
TIMEDIFF(t2.Check_in_time, t1.check_out_time) AS diff
FROM (
SELECT *, @row := @row+1 as rank
FROM table_name, (select @row :=0)
ORDER BY User_ID,Check_in_time
)t1 INNER JOIN (
SELECT *, , @row1 := @row1+1 as rank
FROM table_name, (select @row1 :=0)
ORDER BY User_ID,Check_in_time
)t2 ON t1.User_ID = t2.User_ID AND t1.rank + 1 = t2.rank
WHERE DATE(t2.Check_in_time) = '2019-04-11' AND DATE(t1.check_out_time) = '2019-04-11'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55626921
复制相关文章
相似问题
腾讯云开发者
