如何将Mysql中邻近的两条记录并合并成一条

SQL编写场景

1、有个车辆进出表,需查出车辆进出时间,(其中,carNo车辆编号,time车辆进出时间,falg车辆进出标志,1是代表进场,

2是代表出场)

  2、 希望得到的结果,查出车辆牌号,进场和出场时间,具体如下:

   3、SQL如下:

SELECT
	t1.carNo,
	t1.time AS enter_time,
	(
	SELECT
		t2.time 
	FROM
		car t2 
	WHERE
		t2.flag = '0' 
		AND t1.carNo = t2.carNo 
		AND t2.time > t1.time 
	ORDER BY
		t2.time 
		LIMIT 1 
	) AS out_time 
FROM
	car t1 
WHERE
	t1.flag = '1' 
ORDER BY
	t1.time DESC,
	t1.id DESC;

  

posted @ 2021-12-16 20:16  北国浪子  阅读(646)  评论(0编辑  收藏  举报