处理时间线问题，将拿出时间与放回时间最匹配

Question

问题遇到的现象和发生背景

一台设备有N多个夹子，每个夹子在放入、取出的时候都会记录一条数据

问题相关代码，请勿粘贴截图

现在要获取到每个夹子的使用情况，想将同一设备的同一个夹子的放入拿出操作（一个使用周期）写到一条记录中。

运行结果及报错内容

我的解答思路和尝试过的方法

DROP TABLE IF EXISTS `test_sql`;
CREATE TABLE `test_sql`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `mac_id` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `clip_id` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `action_status` int NULL DEFAULT NULL,
  `action_time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO test_sql (mac_id,clip_id,action_status,action_time) VALUES('001','oe',0,'2022-01-01 01:01:01');
INSERT INTO test_sql (mac_id,clip_id,action_status,action_time) VALUES('001','oe',1,'2022-01-02 01:01:01');
INSERT INTO test_sql (mac_id,clip_id,action_status,action_time) VALUES('001','oe',0,'2022-01-08 01:01:01');
INSERT INTO test_sql (mac_id,clip_id,action_status,action_time) VALUES('001','oe',1,'2022-01-10 01:01:01');
INSERT INTO test_sql (mac_id,clip_id,action_status,action_time) VALUES('001','oe',0,'2022-01-12 01:01:01');

INSERT INTO test_sql (mac_id,clip_id,action_status,action_time) VALUES('001','a1',0,'2022-01-18 01:01:01');
INSERT INTO test_sql (mac_id,clip_id,action_status,action_time) VALUES('001','a1',1,'2022-01-20 01:01:01');
INSERT INTO test_sql (mac_id,clip_id,action_status,action_time) VALUES('001','a1',0,'2022-01-28 01:01:01');


SELECT t1.* FROM test_sql t1; -- 1为拿出，0为放回。给拿出匹配放回。

-- 初步处理 数据非最终想要的数据 因为t2.action_time>=t1.action_time 导致所有大于t1.action_time的t2.action_time 都进行了匹配，导致很多的垃圾数据，只需要取最小的min(t2.action_time) 与t1.action_time 匹配即可
SELECT * FROM test_sql t1 
JOIN (SELECT tmp1.mac_id,tmp1.clip_id,tmp1.action_time FROM test_sql tmp1 WHERE tmp1.action_status=0 
        GROUP BY tmp1.mac_id,tmp1.clip_id,tmp1.action_time ) t2 on t1.mac_id=t2.mac_id AND t1.clip_id=t2.clip_id AND t2.action_time>=t1.action_time
WHERE t1.action_status=1;

-- 二步取最终要的数据 我认为可以优化第一步
SELECT * FROM (
SELECT ROW_NUMBER() over(PARTITION by z.id /*,z.mac_id,z.clip_id,z.action_status,z.action_time,z.action2_time */ ORDER BY z.action_time,z.action2_time ) rn,z.* FROM (
SELECT t1.id,t1.mac_id,t1.clip_id,t1.action_status,t1.action_time,t2.action_time as action2_time  FROM test_sql t1 
JOIN (SELECT tmp1.mac_id,tmp1.clip_id,tmp1.action_time FROM test_sql tmp1 WHERE tmp1.action_status=0 
        GROUP BY tmp1.mac_id,tmp1.clip_id,tmp1.action_time ) t2 on t1.mac_id=t2.mac_id AND t1.clip_id=t2.clip_id AND t2.action_time>=t1.action_time
WHERE t1.action_status=1 ) z ) x
WHERE x.rn=1;

我想要达到的结果

我希望在处理第一步的时候就匹配到对应的最小值，这样就不需要第二步来筛选了。

Answer 1

你随便找个什么高级语言，写一段脚本，分分钟解决问题，根本不用写这么复杂个sql，就一个循环的事