Mysql案例 - 变量使用

问题

现在有两张表，都是自增主键，一张表是记录停车位的预约记录，另一张表记录的是续费记录（即超过预约时长，还需要使用车位，此时用户可以选择续费），两张表的关系是一对多的关系，预约记录中有记录理论上的预约开始时间和预约结束时间（即要使用预约车位的开始和结束时间），但另外一张续费表中，只记录了单次续费的续费时长。

此时，有一个需求要求在查询出的续费记录中，展示续费开始时间和续费到期时间。

解决方案

1、在续费记录中新增两个字段，在新增和查询时对这两个字段操作，旧数据则以脚本的形式补全。
2、在查询记录时，动态计算出来。

这里我选择第二种，因为这个查询只会对单条预约记录产生的续费记录进行查询，数据量不会很大，直接在sql中计算还可以偷点懒

编写查询sql

思路：只有超过了预约结束时间的才需要开始进行续费操作，所以预约结束时间就是第一条续费记录的续费开始时间，然后加上续费时长，就是第一条续费记录的续费到期时间，同时也是第二条续费记录的续费开始时间，然后依此类推就可以得到需要的结果了。
如何判断续费记录的顺序，之前说过记录是自增主键，所以id小的肯定是第一条记录
t_parking_booking 预约记录表

CREATE TABLE `t_parking_booking` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `park_no` varchar(32) DEFAULT NULL COMMENT '车位号',
  `booking_no` varchar(32) NOT NULL COMMENT '预约流水号',
  `booking_date` date DEFAULT NULL COMMENT '预约日期',
  `booking_time` datetime DEFAULT NULL COMMENT '预约时间',
  `booking_end_time` datetime DEFAULT NULL COMMENT '预约结束时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=602 DEFAULT CHARSET=utf8 COMMENT='车位预约记录表';

t_parking_booking_renew 续费记录表

CREATE TABLE `t_parking_booking_renew` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `order_no` varchar(64) DEFAULT NULL COMMENT '续费订单号',
  `booking_id` bigint(20) NOT NULL COMMENT '预约订单id',
  `hours` int(10) DEFAULT NULL COMMENT '续费时长',
  `booking_price` decimal(10,2) DEFAULT NULL COMMENT '续费金额',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=320 DEFAULT CHARSET=utf8 COMMENT='车位预约续费记录表';

查询CWY1660274539288预约记录的续费记录

SELECT
t1.park_no parkNo,
IF(@startTime=0,@startTime:=t1.booking_end_time,@startTime:=@endTime) startTime,
@endTime:=DATE_ADD(@startTime,INTERVAL t1.hours_num HOUR) endTime,
t1.hours hours,
t1.booking_price bookingPrice
FROM
(
	SELECT
	t1.park_no,
	t1.booking_end_time,
	t2.hours,
	t2.booking_price
	FROM
	t_parking_booking t1
	INNER JOIN t_parking_booking_renew t2 ON t1.id = t2.booking_id
	WHERE t1.booking_no = 'CWY1660274539288'
	ORDER BY t2.id
) t1,
(SELECT @startTime:=0,@endTime:=0) r;

parkNo	startTime	endTime	hours	bookingPrice
5号车位	2022-08-13 12:22:21	2022-08-13 13:22:21	1	0.10
5号车位	2022-08-13 13:22:21	2022-08-13 14:22:21	1	0.10

延展：如果是要查询多条预约记录的续费记录

思路：需要找到@startTime的重置点

SELECT
t1.park_no parkNo,
IF(@startTime=0 || @id!=t1.id,@startTime:=t1.booking_end_time,@startTime:=@endTime),
@endTime:=DATE_ADD(@startTime,INTERVAL t1.hours_num HOUR),
t1.hours hours,
t1.booking_price bookingPrice,
IF(@id=t1.id,@id,@id:=t1.id)
FROM
(
	SELECT
	t1.id,
	t1.park_no,
	t2.hours,
	t2.booking_price
	FROM
	t_parking_booking t1
	INNER JOIN t_parking_booking_renew t2 ON t1.id = t2.booking_id
	ORDER BY t2.id
) t1,
(SELECT @startTime:=0,@endTime:=0,@id:=0) r;

parkNo	startTime	endTime	hours	bookingPrice
5号车位	2022-08-13 12:22:21	2022-08-13 13:22:21	1	0.10
5号车位	2022-08-13 13:22:21	2022-08-13 14:22:21	1	0.10
6号车位	2022-08-15 12:22:21	2022-08-15 14:22:21	2	2.00