Mysql实现row_number和lag（）over的功能，不使用变量，求助~

数据如图：
原表
id user_type time
1 A 10:10
1 B 10:14
1 A 10:11
2 B 10:15
1 A 10:13
2 A 10:13
1 B 10:12
1 B 10:15
2 A 10:18
2 B 10:16
1 A 10:16
2 B 10:19

分组排序后
id user_type time
1 A 10:10
1 A 10:11
1 B 10:12
1 A 10:13
1 B 10:14
1 B 10:15
1 A 10:16
2 A 10:13
2 B 10:15
2 B 10:16
2 A 10:18
2 B 10:19

最终结果
id user_type_1 time_1 user_type_2 time_2
1 A 10:11 B 10:12
1 A 10:13 B 10:14
2 A 10:13 B 10:15
2 A 10:18 B 10:19

4 个解决方案

#1

等于就是筛选出相邻的A，B对

#2

SELECT
	s1.id,
	s1.user_type user_type_1,
	s1.time time_1,
	s2.user_type user_type_2,
	s2.time time_2
FROM
(
	SELECT
		(SELECT COUNT(*) FROM temp t2 WHERE t1.id = t2.id AND t1.time >= t2.time) num,
		-- 按id分组，按时间排序，如果分组后的排序字段不唯一可能出错
		t1.*
	FROM
		temp t1
	ORDER BY
		t1.id, t1.time
) s1
INNER JOIN
(
	SELECT
		(SELECT COUNT(*) FROM temp t2 WHERE t1.id = t2.id AND t1.time >= t2.time) num,
		-- 按id分组，按时间排序，如果分组后的排序字段不唯一可能出错
		t1.*
	FROM
		temp t1
	ORDER BY
		t1.id, t1.time
) s2 ON s1.num = s2.num - 1 AND s1.id = s2.id AND s1.user_type = 'A' AND s2.user_type = 'B'

+----+-------------+----------+-------------+----------+
| id | user_type_1 | time_1   | user_type_2 | time_2   |
+----+-------------+----------+-------------+----------+
|  1 | A           | 10:11:00 | B           | 10:12:00 |
|  1 | A           | 10:13:00 | B           | 10:14:00 |
|  2 | A           | 10:13:00 | B           | 10:15:00 |
|  2 | A           | 10:18:00 | B           | 10:19:00 |
+----+-------------+----------+-------------+----------+
4 rows in set (0.07 sec)

附上建表语句

CREATE TABLE `temp` (
  `id` int(11) NOT NULL,
  `user_type` char(1) NOT NULL,
  `time` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test`.`temp`(`id`, `user_type`, `time`) VALUES (1, 'A', '10:10:00');
INSERT INTO `test`.`temp`(`id`, `user_type`, `time`) VALUES (1, 'B', '10:14:00');
INSERT INTO `test`.`temp`(`id`, `user_type`, `time`) VALUES (1, 'A', '10:11:00');
INSERT INTO `test`.`temp`(`id`, `user_type`, `time`) VALUES (2, 'B', '10:15:00');
INSERT INTO `test`.`temp`(`id`, `user_type`, `time`) VALUES (1, 'A', '10:13:00');
INSERT INTO `test`.`temp`(`id`, `user_type`, `time`) VALUES (2, 'A', '10:13:00');
INSERT INTO `test`.`temp`(`id`, `user_type`, `time`) VALUES (1, 'B', '10:12:00');
INSERT INTO `test`.`temp`(`id`, `user_type`, `time`) VALUES (1, 'B', '10:15:00');
INSERT INTO `test`.`temp`(`id`, `user_type`, `time`) VALUES (2, 'A', '10:18:00');
INSERT INTO `test`.`temp`(`id`, `user_type`, `time`) VALUES (2, 'B', '10:16:00');
INSERT INTO `test`.`temp`(`id`, `user_type`, `time`) VALUES (1, 'A', '10:16:00');
INSERT INTO `test`.`temp`(`id`, `user_type`, `time`) VALUES (2, 'B', '10:19:00');

#3

安利一下，《Mysql实现排名函数的三种方法》
https://blog.csdn.net/mingqing6364/article/details/82621840

#4

引用 3 楼 mingqing6364 的回复:

安利一下，《Mysql实现排名函数的三种方法》
https://blog.csdn.net/mingqing6364/article/details/82621840

感谢~