MySQL 两张表数据合并的实现

2023-01-05 12:00

短信预约 -IT技能 免费直播动态提醒

有一个需求, 需要从数据库中导出两张表的数据到同一个excel中

鉴于是临时的业务需求, 直接使用Navicat 进行查询并导出数据.

数据涉及到三张表

CREATE TABLE `bigdata_qiye` (
  `id` bigint(64) NOT NULL COMMENT '主键',
  `tenant_id` varchar(12) DEFAULT '000000' COMMENT '租户ID',
  `registration_type` int(2) DEFAULT NULL COMMENT '注册类型（1.国有，2.民营，3.外资）',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `bigdata_qiye_tenant_id` (`tenant_id`) USING BTREE,
  KEY `bigdata_qiye_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='申报企业表';

CREATE TABLE `bigdata_qiye_report` (
  `id` bigint(64) NOT NULL COMMENT '主键',
  `tenant_id` varchar(12) DEFAULT '000000' COMMENT '租户ID',
  `qiye_id` bigint(64) DEFAULT '0' COMMENT '企业扩展信息',
  `revenue` double(16,2) DEFAULT NULL COMMENT '营收',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `bqr_qiye_id` (`qiye_id`) USING BTREE,
  KEY `bgr_tenant_id` (`tenant_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='企业申报信息表';

CREATE TABLE `bigdata_tech_improve_impl` (
  `id` bigint(64) unsigned zerofill NOT NULL COMMENT '主键',
  `tenant_id` varchar(12) DEFAULT '000000' COMMENT '租户ID',
  `qiye_id` bigint(64) DEFAULT '0' COMMENT '企业扩展信息',
  `total_input` decimal(64,2) DEFAULT NULL COMMENT '总投资',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='技改项目实施情况表';

需要合并导出 bigdata_qiye_report 表与 bigdata_tech_improve_impl 表的数据

表 bigdata_qiye 与表 bigdata_qiye_report 是一对多的关系
表 bigdata_qiye 与表 bigdata_tech_improve_impl 也是一对多的关系
表 bigdata_qiye_report 与表 bigdata_tech_improve_impl 没有关联关系

希望导出的excel格式

在这里插入图片描述

所以, 如果用链接查询的话产生的结果会偏差
比如这样

select bq.registration_type ,
bqr.revenue,
btii.total_input
from bigdata_qiye bq 
left join bigdata_qiye_report bqr
on bqr.qiye_id = bq.id
left join bigdata_tech_improve_impl btii
on btii.qiye_id = bq.id

在这里插入图片描述

会产生许多的重复数据 .

解决方法
使用 union(对结果集去重) 或者 union all(不去重) 关键字将两个 select 语句的结果作为一个整体显示出来

第一个sql

select 
case 
bq.registration_type 
when 1 then '国有'
when 2 then '民营'
when 3 then '外资'
else ''
end as '注册类型',
bqr.revenue as '营收'
from bigdata_qiye bq 
left join bigdata_qiye_report bqr
on bqr.qiye_id = bq.id

第二个sql

select 
case 
bq.registration_type 
when 1 then '国有'
when 2 then '民营'
when 3 then '外资'
else ''
end as '注册类型',
btii.total_input as '总资产'
from bigdata_qiye bq 
left join bigdata_tech_improve_impl btii
on btii.qiye_id = bq.id

合并 SQL


(select 
case 
bq.registration_type 
when 1 then '国有'
when 2 then '民营'
when 3 then '外资'
else ''
end as '注册类型',
bqr.revenue as '营收'
from bigdata_qiye bq 
left join bigdata_qiye_report bqr
on bqr.qiye_id = bq.id) 

union all

(select 
case 
bq.registration_type 
when 1 then '国有'
when 2 then '民营'
when 3 then '外资'
else ''
end as '注册类型'
btii.total_input as '总资产'
from bigdata_qiye bq 
left join bigdata_tech_improve_impl btii
on btii.qiye_id = bq.id)

执行, 报错
原因: 使用 union 关键字时, 必须要保证两张表的字段一模一样(包括顺序)
所以修改sql
sql _1 修改

select 
case 
bq.registration_type 
when 1 then '国有'
when 2 then '民营'
when 3 then '外资'
else ''
end as '注册类型',
bqr.revenue as '营收',
'' as '总资产'
from bigdata_qiye bq 
left join bigdata_qiye_report bqr
on bqr.qiye_id = bq.id

sql_2修改

select 
case 
bq.registration_type 
when 1 then '国有'
when 2 then '民营'
when 3 then '外资'
else ''
end as '注册类型',
'' as '营收',
btii.total_input as '总资产'
from bigdata_qiye bq 
left join bigdata_tech_improve_impl btii
on btii.qiye_id = bq.id

合并SQL


(select 
case 
bq.registration_type 
when 1 then '国有'
when 2 then '民营'
when 3 then '外资'
else ''
end as '注册类型',
bqr.revenue as '营收',
'' as '总资产'
from bigdata_qiye bq 
left join bigdata_qiye_report bqr
on bqr.qiye_id = bq.id) 

union all

(select 
case 
bq.registration_type 
when 1 then '国有'
when 2 then '民营'
when 3 then '外资'
else ''
end as '注册类型',
'' as '营收',
btii.total_input as '总资产'
from bigdata_qiye bq 
left join bigdata_tech_improve_impl btii
on btii.qiye_id = bq.id)

查询结果

在这里插入图片描述