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

Question

需求描述

我有两张表

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

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

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

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

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

假如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"
UNION ALL
SELECT rtp.license_number, rtp.begin_time, rtp.entry_time FROM refuse_treatment_plant rtp where rtp.license_number="京OFV501FV606"

select license_number, begin_time, entry_time
from (SELECT bi.license_number, bi.begin_time, bi.entry_time FROM bulldozer_info bi
where bi.license_number="京OFV501FV606"
UNION
SELECT rtp.license_number, rtp.begin_time, rtp.entry_time FROM refuse_treatment_plant rtp
where rtp.license_number="京OFV501FV606") t

表的结构

-- ----------------------------
-- 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='车辆进消纳厂记录';

Answer 1

select *
from (select bi.license_number  as bilicense_number,
             rtp.license_number as rtplicense_number,
             bi.begin_time,
             bi.entry_time,
             rtp.begin_time,
             rtp.entry_time
      FROM bulldozer_info bi
               right join refuse_treatment_plant rtp on bi.license_number = rtp.license_number
      union
      select bi.license_number  as bilicense_number,
             rtp.license_number as rtplicense_number,
             bi.begin_time,
             bi.entry_time,
             rtp.begin_time,
             rtp.entry_time
      FROM bulldozer_info bi
               left join refuse_treatment_plant rtp on bi.license_number = rtp.license_number) as birtp
where bilicense_number = '京OFV501FV606'
   or rtplicense_number = '京OFV501FV606'