mysql - 显示日期和时间差异的下一行

Question

我有以下架构和查询:

架构:

CREATE TABLE IF NOT EXISTS `timerecord` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rfidid` bigint(20) NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4796 DEFAULT CHARSET=latin1;


INSERT IGNORE INTO `timerecord` (`id`, `rfidid`, `timestamp`) VALUES
    (1, 303840239533, '2019-12-12 05:14:20'),
    (2, 788305330516, '2019-12-12 05:48:30'),
    (3, 507852168974, '2019-12-12 13:46:49'),
    (4, 854049708561, '2019-12-12 13:45:26'),
    (5, 894619595089, '2019-12-12 13:49:01'),
    (6, 98305074150, '2019-12-12 13:49:29'),
    (7, 370080547320, '2019-12-12 13:50:46'),
    (8, 234926911496, '2019-12-12 13:50:48'),
    (9, 138994951270, '2019-12-12 13:51:18'),
    (10, 759543349144, '2019-12-12 13:53:24'),
    (11, 209233748074, '2019-12-12 13:53:48'),
    (12, 788305330516, '2019-12-12 14:01:51'),
    (13, 303840239533, '2019-12-12 14:19:17'),
    (1060, 852998227698, '2019-12-20 14:00:03'),
    (1061, 303840239533, '2019-12-20 14:00:23'),
    (1062, 345225012374, '2019-12-20 14:00:26'),
    (1063, 507016589505, '2019-12-20 14:00:38'),
    (1064, 988475989404, '2019-12-20 14:00:56'),
    (1065, 688343855400, '2019-12-20 14:02:08'),
    (1066, 207849436459, '2019-12-20 14:02:22'),
    (1067, 275996325150, '2019-12-20 14:02:52'),
    (1068, 207046160732, '2019-12-20 14:03:20'),
    (1069, 303840239533, '2019-12-20 14:03:28'),
    (1070, 553885999610, '2019-12-20 14:03:32'),
    (1071, 210385347481, '2019-12-20 14:03:36'),
    (1072, 919633604759, '2019-12-20 14:03:41'),
    (1073, 27945115058, '2019-12-20 14:03:53'),
    (1074, 95726181755, '2019-12-20 14:03:54'),
    (1075, 989836095604, '2019-12-20 14:04:01'),
    (1076, 962275222618, '2019-12-20 14:00:29'),
    (1077, 28697451122, '2019-12-20 14:04:05'),
    (1078, 69185806432, '2019-12-20 14:00:40'),
    (1079, 895597429714, '2019-12-20 14:04:15'),
    (1080, 71190815850, '2019-12-20 14:00:42'),
    (1081, 27057205446, '2019-12-20 14:05:16'),
    (1082, 170651730431, '2019-12-20 14:00:45'),

    (1103, 97253535809, '2019-12-20 14:13:29'),
    (1104, 894284087770, '2019-12-20 14:02:13'),
    (1105, 168336212297, '2019-12-20 14:14:28'),
    (1106, 897165665450, '2019-12-20 14:14:35'),
    (1107, 278193390687, '2019-12-20 14:14:47'),
    (1108, 551520150885, '2019-12-20 14:14:49'),
    (1109, 578270135989, '2019-12-20 14:02:54'),
    (1110, 346629059046, '2019-12-20 14:03:22'),
    (1111, 851436591666, '2019-12-20 14:03:28'),
    (1112, 276090892789, '2019-12-20 14:03:32'),
    (1113, 303190443608, '2019-12-20 14:03:40'),
    (1114, 852428206112, '2019-12-20 14:04:33'),
    (1115, 29000925520, '2019-12-20 14:05:37'),
    (1116, 235096584291, '2019-12-20 14:08:09'),
    (1117, 483995529488, '2019-12-20 14:14:40'),
    (1118, 621443462527, '2019-12-20 14:15:50'),
    (1119, 553247745495, '2019-12-20 14:16:40'),
    (1120, 235700904554, '2019-12-20 14:16:06'),
    (1121, 921408789050, '2019-12-20 14:19:21'),
    (1122, 303840239533, '2019-12-20 14:17:07'),
    (2684, 207046160732, '2020-01-09 06:00:55'),
    (2685, 140236797424, '2020-01-09 06:00:58'),   
    (4795, 647093104846, '2020-01-14 14:00:00');

查询:

select * 
     , (@ind := @ind + 1) as k
     , CASE WHEN MOD(@ind,2) THEN 'in' ELSE 'out' END as status 
  from timerecord
     , (SELECT @ind:=0) set_var 
 where rfidid LIKE '303840239533%' 
 order 
    by `TIMESTAMP`

http://sqlfiddle.com/#!9/16401/1/0

如何显示给定 id 从 2019-20-20 开始的下一行，并获取入行和出行之间的时间差

Answer 1

您可以使用下一个查询来实现:

SELECT
    first.rfidid, 
    first.id, 
    last.id, 
    timediff(first.timestamp, last.timestamp) -- calculate time diff
FROM (
    -- get first row with requested rfidid and id
    SELECT * FROM timerecord WHERE  rfidid = '303840239533' AND id = 1061
) as first
JOIN (
    -- get next row 
    SELECT * FROM timerecord WHERE  rfidid = '303840239533' AND id > 1061 ORDER BY id ASC LIMIT 1
) as last on first.rfidid = last.rfidid;