需求描述
我有三张表
bulldozer_standing_book
车辆表 bulldozer_info
车辆离开工厂表refuse_treatment_plant
车辆进消纳场表
我希望查询当前,在车辆表中所有车辆的进入总数leave_count
、出入总数entry_count
现在逻辑
现在查询语句是查询所有天的总次数,应该如何改
SELECT bsb.id, bsb.license_number AS licenseNumber, bsb.data_source AS dataSource, bsb.create_time AS createTime,
bi.begin_time, bi.entry_time, rtp.begin_time, rtp.entry_time,
bsb.update_time AS updateTime, IFNULL(leave_count, 0) AS leaveCount, COALESCE(entry_count, 0) AS entryCount
FROM bulldozer_standing_book bsb
left join (
select count(*) leave_count, license_number, begin_time, entry_time from bulldozer_info group by license_number
) bi on bi.license_number = bsb.license_number
left join (
select count(*) entry_count, license_number,begin_time, entry_time from refuse_treatment_plant group by license_number
) rtp on rtp.license_number = bsb.license_number
ORDER BY bsb.update_time DESC;
自己尝试加where,但是一直报错
表的结构
-- ----------------------------
-- 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='车辆进消纳厂记录';