Spring Boot我有三张表，希望当天leave_count 和 entry_count的总数

Question

需求描述

我有三张表

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

Answer 1

# 根据你的逻辑，我修改了以下地方
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 AND TO_DAYS(bi.begin_time) = TO_DAYS(now()) -- 此处修改
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 AND TO_DAYS(rtp.begin_time) = TO_DAYS(now()) -- 此处修改
ORDER BY
    bsb.update_time DESC;

Answer 2

你的两个left join的右表语法都不对，使用group分组后，select列只能出现被分组的列和统计函数，比如在你的sql里就是license_number和count(*)。
不明白bi和rtp中的时间是取的什么值。
时间条件应该放在bi和rtp中。

SELECT
    bsb.id,
    bsb.license_number AS licenseNumber,
    bsb.data_source AS dataSource,
    bsb.create_time AS createTime,
    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
    from
        bulldozer_info
    WHERE 时间条件
    group by
        license_number ) bi on
    bi.license_number = bsb.license_number
left join (
    select
        count(*) entry_count,
        license_number
    from
        refuse_treatment_plant
    WHERE 时间条件
    group by
        license_number ) rtp on
    rtp.license_number = bsb.license_number
ORDER BY
    bsb.update_time DESC;