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

小刘
  • 526

我有三张表

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

就是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;

回复
阅读 1.4k
3 个回答
✓ 已被采纳

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

bulldozer_standing_book

idlicense_number
1a
2b
3c
5d

refuse_treatment_plant

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

bulldozer_info

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

查询语句

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;

查询结果

idlicense_numberbi.totalrtp.total
1a35
2b42
3c32
5d43

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

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

是这个意思吗

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
宣传栏