将基于日期范围的两个数据表连接为MySQL中的唯一标准

Question

我在MySQL中有两个表：

CREATE TABLE `imported_files` (
  `import_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `recorded_datetime` datetime DEFAULT NULL,
  `inserted_datetime` datetime DEFAULT NULL,
  `filename` varchar(255) DEFAULT NULL,
  `condition` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`import_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `imported_files` (`import_id`, `recorded_datetime`, `inserted_datetime`, `filename`, `condition`)
VALUES
    (293,'2019-07-20 13:51:00','2019-07-20 22:36:15','./RFID 2019-07-20 13_51.csv','rfid'),
    (294,'2019-07-20 14:32:00','2019-07-20 22:36:20','./RFID 2019-07-20 14_32.csv','rfid'),
    (295,'2019-07-20 13:52:00','2019-07-20 22:36:15','./RFID 2019-07-20 13_51.csv','rfid');

CREATE TABLE `RFID` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `SortedNumber` int(11) unsigned DEFAULT NULL,
  `Time` datetime DEFAULT NULL,
  `UID` varchar(30) DEFAULT NULL,
  `Antenna` tinyint(3) unsigned DEFAULT NULL,
  `Activity` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `RFID` (`id`, `SortedNumber`, `Time`, `UID`, `Antenna`, `Activity`)
VALUES
    (161,19,'2019-07-20 14:36:17','A0 1 3 C0 0 40 11 1E',1,'Tunnel'),
    (162,18,'2019-07-20 14:23:08','A0 1 3 C0 0 40 E1 8A',1,'Tunnel'),
    (163,19,'2019-07-20 12:00:00','A0 1 3 C0 0 40 E1 8A',2,'Tunnel');

我希望将imported_files表中的import_id与RFID表中的UID值相关联。我可以在一定程度上实现它，为日期范围生成import_id，如下所示：

SELECT import_id 
FROM imported_files 
WHERE recorded_datetime >= (SELECT `Time` FROM `RFID` ORDER BY `Time` LIMIT 1  OFFSET 1) 
  AND recorded_datetime <= (SELECT `Time` FROM `RFID` ORDER BY `Time` LIMIT 1 OFFSET 17);

如何使用import_id加入UID？这样我可能会有如下的输出：

import_id  UID  
---------  --------------------
294        A0 1 3 C0 0 40 E1 8A

Answer 1

使用两步法，可以在与imported_files表中的时间戳相关联的RFID表中找到UID：

步骤1:从RFID表中识别与日期时间关联的import_id：

SELECT import_id FROM imported_files 
WHERE recorded_datetime >=  (SELECT `Time` FROM `RFID` ORDER BY `Time` LIMIT 1  OFFSET 17) 
AND recorded_datetime <= (SELECT `Time` FROM `RFID` ORDER BY `Time` LIMIT 1 OFFSET 18);

第2步:查找与特定import_id关联的UID：

SELECT UID,`Time` FROM RFID 
WHERE `Time` <= (SELECT recorded_datetime FROM imported_files WHERE import_id = 294) 
ORDER BY `Time` 
DESC LIMIT 1;

有没有更有效的方法？