MySQL我想将这三张表中的数据一起查询出来，统计其中一个表中的数据在另外两张表的出现次数

Question

我有三张表

我想将这三张表中的数据一起查询出来

就是bulldozer_standing_book中记录的车牌号，在refuse_treatment_plant和bulldozer_info出现的次数

-- ----------------------------
-- 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 '车辆行驶状态',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13223 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=48 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 '车辆行驶状态',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=154 DEFAULT CHARSET=utf8 COMMENT='车辆进消纳厂记录';

bulldozer_info出工地表，有1万条数据

refuse_treatment_plant进消纳厂表，有100条数据

bulldozer_standing_book车牌号记录表

我想将这三张表中的数据一起查询出来

就是bulldozer_standing_book中记录的车牌号，在refuse_treatment_plant和bulldozer_info出现的次数

两张表一起查询已经实现，但是三张表的查询怎么写MySQL语句

两张表的查询语句

SELECT id, license_number from bulldozer_standing_book as bsb left join(select license_number, count(*) as entry_count from refuse_treatment_plant group by license_number) as rtp on bsb.license_number=rtp.license_number

我想这么实现

SELECT * FROM
(
SELECT id, license_number from bulldozer_standing_book as bsb left join(select license_number, count(*) as entry_count from refuse_treatment_plant group by license_number) as rtp on bsb.license_number=rtp.license_number

) a1 LEFT JOIN 
(
　　select license_number, count(*) as leave_count from bulldozer_info group by license_number
) a2 ON a1.license_number = a2.license_number;

又改了一下，还是报错中

SELECT * FROM
(
SELECT id, bsb.license_number from bulldozer_standing_book as bsb left join(select license_number, count(*) as entry_count from refuse_treatment_plant group by license_number) as rtp on bsb.license_number=rtp.license_number
) a1 
LEFT JOIN 
(
　　select license_number, count(*) as leave_count from bulldozer_info group by license_number
) a2 ON a1.license_number = a2.license_number;

这是两个表联合查询的结果

然后我还要从 bulldozer_info 获取一下leave_count

三张表一起查不太会写

SELECT id, bsb.license_number, entry_count from bulldozer_standing_book as bsb left join(select license_number, count(*) as entry_count from refuse_treatment_plant group by license_number) as rtp on bsb.license_number=rtp.license_number

感谢楼下的答案

不过那么查询会出现null，所以我又改进下

select bsb.*, IFNULL(leave_count, 0), COALESCE(entry_count, 0)
from bulldozer_standing_book bsb
         left join (
    select count(*) leave_count, license_number from bulldozer_info group by license_number
) bi on bi.license_number = bsb.license_number
         left join (
    select count(*) entry_count, license_number from refuse_treatment_plant group by license_number
) rtp on rtp.license_number = bsb.license_number;

Answer 1

看了你的问题后，为了方便，为了排除其他干扰，我忽略了其他数据，只处理你需求上的逻辑，然后对数据表做了简化，每个表我只存放license_number字段，简化后的表结构和数据如下：

bulldozer_standing_book

id	license_number
1	a
2	b
3	c
5	d

refuse_treatment_plant

id	license_number
1	a
2	b
3	c
4	d
5	a
6	c
7	a
8	b
9	d
10	d
11	a
12	a

bulldozer_info

id	license_number
1	a
2	b
3	b
4	c
5	a
6	d
7	d
8	d
9	c
10	a
11	c
12	d
13	b
14	b

查询语句

select bsb.*, bi.total, rtp.total
from bulldozer_standing_book bsb
         left join (
    select count(*) total, license_number from bulldozer_info group by license_number
) bi on bi.license_number = bsb.license_number
         left join (
    select count(*) total, license_number from refuse_treatment_plant group by license_number
) rtp on rtp.license_number = bsb.license_number;

查询结果

id	license_number	bi.total	rtp.total
1	a	3	5
2	b	4	2
3	c	3	2
5	d	4	3

Answer 2

上次不有人和你说了嘛
UNION ALL，
或者把你想要的查询结果丢出来，合在一起，可以是行合在一起，也可以是某些列合并到一个表格中来

Answer 3

select license_number,count(*) from (select * from refuse_treatment_plant where license_number in (select license_number from bulldozer_info) group by license_number) as a

是这个意思吗