mysql - 我想找到一种方法来在 1 个 mysql 查询中获得合适的结果

Question

我有一个表名 order_history我存储两者的地方 old_status和 new_status公司订单。
表的架构:

CREATE TABLE order_history (
  id int(11) NOT NULL AUTO_INCREMENT,
  old_status longtext COLLATE utf8_unicode_ci,
  new_status longtext COLLATE utf8_unicode_ci,
  created_at datetime NOT NULL,
  order_id int(11) DEFAULT NULL,
  PRIMARY KEY (id)
 }

要填充的插入是:

INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (1, '56', '714', '2020-12-20 21:37:54', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (2, '714', '61', '2020-12-20 21:37:56', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (3, '61', '713', '2020-12-20 21:38:17', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (4, '713', '42', '2020-12-20 21:38:26', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (5, '42', '51', '2020-12-20 21:59:17', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (6, '56', '714', '2020-12-20 22:21:27', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (7, '714', '61', '2020-12-20 22:21:29', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (8, '61', '713', '2020-12-20 22:24:28', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (9, '713', '42', '2020-12-20 22:24:43', 94471496);

现在我想找到的问题 TIMEDIFF的 created_at s 行之间 new_status=61和行 new_status=42 and old_status=713 .
所以在这个例子中，受影响的行应该是 (2,4,7,9) ，正确的答案是 TIMEDIFF在具有 ids (2,4) 的行和具有 ids (7,9) 的行之间。但是我的查询返回 3 个结果而不是 2 个结果，它还计算了 TIMEDIFF行 (2,9) 之间。
我怎样才能排除这个结果？
这是我的查询:

select *
from (select oschStart.order_id as order_id, TIMEDIFF(oschEnd.created_at, oschStart.created_at) as confirm_time
      from (select osch1.order_id, osch1.created_at
            from order_history osch1
            where osch1.old_status = 713
              and osch1.new_status = 42
           ) oschEnd
               join (select osch1.order_id, osch1.created_at
                     from order_history osch1
                     where osch1.new_status = 61
      ) oschStart
                    on oschStart.order_id = oschEnd.order_id and oschEnd.created_at > oschStart.created_at) order_time;

Answer 1

一种更简单的方法是使用相关子查询

select *,
        timediff(
        (select created_at from order_history oh1 
            where oh1.order_id = oh.order_id and
                  oh1.id > oh.id and
                  oh1.old_status = '713' and oh1.new_status = '42'
         order by oh1.id asc limit 1),oh.created_at) diff
from order_history oh
where  new_status = 61;