mysql 分组计算耗时

Question

有一张单据审批表需要计算开始提交时间到最后时间耗时情况和每一步的耗时 以小时为单位谢谢， 想要的结果

INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZTJC-LHR-20220816-046', NULL, 'Usama Ahsan', '2022-08-16 21:19:49');
INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZTJC-LHR-20220816-046', '1级审批', '李阳（Li Yang）', '2022-08-16 21:39:14');
INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZTJC-LHR-20220816-046', '1级审批', '贺凯', '2022-08-17 12:41:30');
INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZTJC-LHR-20220816-046', '1级审批', '马作腾（Ma Zuoteng）', '2022-08-17 12:52:07');
INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZTJC-LHR-20220816-046', '2级审批', '徐鸿昌（Xu Hongchang）', '2022-08-17 15:15:45');
INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZTJC-LHR-20220816-046', '3级审批', '邱长斌（Qiu Changbin）', '2022-08-17 15:41:37');
INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZTJC-LHR-20220816-046', '4级审批', '范艳（Fan Yan）', '2022-08-17 15:49:20');
INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZSDP-LHR-20220912-296', NULL, 'Salman Shahzad', '2022-09-12 19:42:21');
INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZSDP-LHR-20220912-296', '1级审批', '李阳（Li Yang）', '2022-09-12 19:51:36');
INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZSDP-LHR-20220912-296', '1级审批', '贺凯', '2022-09-12 19:52:31');
INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZSDP-LHR-20220912-296', '1级审批', '徐鸿昌（Xu Hongchang）', '2022-09-12 20:20:27');
INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZSDP-LHR-20220912-296', '1级审批', '马作腾（Ma Zuoteng）', '2022-09-12 21:30:43');
INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZSDP-LHR-20220912-296', '2级审批', '邱长斌（Qiu Changbin）', '2022-09-12 21:31:02');
INSERT INTO `ksa`.`haos`(`salesorder_no`, `name`, `last_name`, `createdtime`) VALUES ('ZSDP-LHR-20220912-296', '3级审批', '范艳（Fan Yan）', '2022-09-12 22:17:20');

Answer 1

语句如下：

root [test]> select salesorder_no, name, hour(timediff(max(createdtime), min(createdtime))) time_cost from haos group by salesorder_no, name;
+-----------------------+------------+-----------+
| salesorder_no         | name       | time_cost |
+-----------------------+------------+-----------+
| ZTJC-LHR-20220816-046 | NULL       |         0 |
| ZTJC-LHR-20220816-046 | 1级审批    |        15 |
| ZTJC-LHR-20220816-046 | 2级审批    |         0 |
| ZTJC-LHR-20220816-046 | 3级审批    |         0 |
| ZTJC-LHR-20220816-046 | 4级审批    |         0 |
| ZSDP-LHR-20220912-296 | NULL       |         0 |
| ZSDP-LHR-20220912-296 | 1级审批    |         1 |
| ZSDP-LHR-20220912-296 | 2级审批    |         0 |
| ZSDP-LHR-20220912-296 | 3级审批    |         0 |
+-----------------------+------------+-----------+
9 rows in set (0.00 sec)