MySQL 两张表数据合并的实现

有一个需求, 需要从数据库中导出两张表的数据到同一个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)

查询结果

到此这篇关于MySQL 两张表数据合并的实现的文章就介绍到这了,更多相关MySQL 数据合并内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！