SpringBoot查询数据库，如何根据日期与车牌号查询将两张表进出的详细信息

Question

需求描述

我有两张表

bulldozer_info 车辆离开工厂表
refuse_treatment_plant 车辆进消纳场表

我希望根据日期与车牌号查询这两张表进出的详细信息

bulldozer_info查询出厂时间 查询begin_time出场时间
refuse_treatment_plant查询到场时间 查询entry_time进进场时间

这两个的数量不一定，有时是出厂的次数大于进场的次数，有时是进场的次数大于出厂的次数

显示的行数与次数多的相等

假如bulldozer_info 有两条数据， refuse_treatment_plant 有三条数据，那么就一共显示三条数据，

效果图如下

begin_time	entry_time
时间 1	时间2
时间 3	时间 4
null	时间 5

或者

begin_time	entry_time
时间 1	时间2
时间 3	时间 4
时间 5	null

我的尝试

尝试多次依然无法得到我想要的结果

SELECT 
bi.license_number, bi.begin_time, bi.entry_time
FROM bulldozer_info bi WHERE bi.license_number="京OFV501FV606"

SELECT 
rtp.license_number, rtp.begin_time, rtp.entry_time 
FROM refuse_treatment_plant rtp WHERE rtp.license_number="京OFV501FV606"

SELECT A.begin_time, A.entry_time, B.entry_time FROM
(SELECT 
license_number, bi.begin_time , bi.entry_time
FROM bulldozer_info bi WHERE bi.license_number="京OFV501FV606") AS A,

(SELECT 
license_number, rtp.entry_time
FROM refuse_treatment_plant rtp WHERE rtp .license_number="京OFV501FV606") AS B 

WHERE A.license_number=B.license_number

SELECT 
bi.license_number, bi.begin_time, bi.entry_time, rtp.entry_time 
FROM bulldozer_info bi 
LEFT JOIN refuse_treatment_plant rtp 
ON bi.license_number=rtp.license_number
WHERE bi.license_number="京OFV501FV606"
UNION
SELECT 
bi.license_number, bi.begin_time, bi.entry_time, rtp.entry_time 
FROM bulldozer_info bi 
RIGHT JOIN refuse_treatment_plant rtp 
ON bi.license_number=rtp.license_number
WHERE bi.license_number="京OFV501FV606" GROUP BY rtp.id

表的结构

-- ----------------------------
-- Table structure for bulldozer_info
-- ----------------------------
DROP TABLE IF EXISTS `bulldozer_info`;
CREATE TABLE `bulldozer_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '车辆ID',
  `license_number` varchar(50) NOT NULL COMMENT '车牌号',
  `data_source` int(2) DEFAULT '0' COMMENT '数据来源',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `begin_time` varchar(50) DEFAULT NULL COMMENT '出发时间',
  `end_time` varchar(50) DEFAULT NULL COMMENT '到达时间',
  `elimination` varchar(50) DEFAULT NULL COMMENT '消纳地点',
  `rubbish_type` varchar(50) DEFAULT NULL COMMENT '垃圾类型',
  `order_number` varchar(100) DEFAULT NULL COMMENT '运单编号',
  `site_name` varchar(50) DEFAULT NULL COMMENT '工地名称',
  `site_area` varchar(20) DEFAULT NULL COMMENT '工地所属区',
  `elimination_area` varchar(20) DEFAULT NULL COMMENT '消纳地点所属区',
  `transport_name` varchar(50) DEFAULT NULL COMMENT '运输企业',
  `transport_area` varchar(20) DEFAULT NULL COMMENT '运输企业所属区',
  `transport_volume` float(10,2) DEFAULT NULL COMMENT '运输量(吨)',
  `driving_status` int(11) DEFAULT '2' COMMENT '车辆行驶状态',
  `entry_time` timestamp NULL DEFAULT NULL COMMENT '进消纳厂时间',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13239 DEFAULT CHARSET=utf8 COMMENT='渣土车信息表';

-- ----------------------------
-- Table structure for bulldozer_standing_book
-- ----------------------------
DROP TABLE IF EXISTS `bulldozer_standing_book`;
CREATE TABLE `bulldozer_standing_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '台账ID',
  `license_number` varchar(50) NOT NULL COMMENT '车牌号',
  `data_source` int(2) DEFAULT '0' COMMENT '数据来源',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=utf8 COMMENT='渣土车信台账表';

-- ----------------------------
-- Table structure for refuse_treatment_plant
-- ----------------------------
DROP TABLE IF EXISTS `refuse_treatment_plant`;
CREATE TABLE `refuse_treatment_plant` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '进消纳厂记录ID',
  `license_number` varchar(50) NOT NULL COMMENT '车牌号',
  `plant_name` varchar(100) DEFAULT NULL COMMENT '消纳厂名称',
  `entry_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '进消纳厂时间',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `license_path` varchar(300) DEFAULT NULL COMMENT '车牌抓拍图片路径',
  `driving_status` int(11) DEFAULT '1' COMMENT '车辆行驶状态',
  `begin_time` timestamp NULL DEFAULT NULL COMMENT '离开工地时间',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=168 DEFAULT CHARSET=utf8 COMMENT='车辆进消纳厂记录';

set @num1 = 0,@num2 = 0,@num3 = 0,@num4 = 0;
select     t1.license_number,t1.begin_time,t2.entry_time
FROM
(SELECT (@num1 := @num1 + 1) AS number,bulldozer_info.* FROM bulldozer_info WHERE license_number = "京OFV501FV606" ORDER BY BEGIN_time ) t1 
LEFT JOIN
(SELECT (@num2 := @num2 + 1) AS number,refuse_treatment_plant.* FROM refuse_treatment_plant where license_number = "京OFV501FV606" ORDER BY entry_time ) t2
on t1.license_number = t2.license_number  and t1.number = t2.number

UNION
select     t1.license_number,t1.begin_time,t2.entry_time
FROM
(SELECT (@num3 := @num3 + 1) AS number,bulldozer_info.* FROM bulldozer_info WHERE license_number = "京OFV501FV606" ORDER BY BEGIN_time ) t1 
RIGHT  JOIN
(SELECT (@num4 := @num4 + 1) AS number,refuse_treatment_plant.* FROM refuse_treatment_plant where license_number = "京OFV501FV606" ORDER BY entry_time ) t2
on t1.license_number = t2.license_number  and t1.number = t2.number