MySQL 兩張表數(shù)據(jù)合并的實現(xiàn)

更新時間：2023年01月05日 10:57:55 作者：Dylan Song

本文主要介紹了MySQL 兩張表數(shù)據(jù)合并的實現(xiàn)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧

有一個需求, 需要從數(shù)據(jù)庫中導出兩張表的數(shù)據(jù)到同一個excel中

鑒于是臨時的業(yè)務需求, 直接使用Navicat 進行查詢并導出數(shù)據(jù).

數(shù)據(jù)涉及到三張表

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='申報企業(yè)表';

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 '企業(yè)擴展信息',
  `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='企業(yè)申報信息表';

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 '企業(yè)擴展信息',
  `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 表的數(shù)據(jù)

表 bigdata_qiye 與表 bigdata_qiye_report 是一對多的關(guān)系
表 bigdata_qiye 與表 bigdata_tech_improve_impl 也是一對多的關(guān)系
表 bigdata_qiye_report 與表 bigdata_tech_improve_impl 沒有關(guān)聯(lián)關(guān)系

希望導出的excel格式

在這里插入圖片描述

所以, 如果用鏈接查詢的話產(chǎn)生的結(jié)果會偏差
比如這樣

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

在這里插入圖片描述

會產(chǎn)生許多的重復數(shù)據(jù) .

解決方法
使用 union(對結(jié)果集去重) 或者 union all(不去重) 關(guān)鍵字將兩個 select 語句的結(jié)果作為一個整體顯示出來

第一個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 '總資產(chǎn)'
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 '總資產(chǎn)'
from bigdata_qiye bq 
left join bigdata_tech_improve_impl btii
on btii.qiye_id = bq.id)

執(zhí)行, 報錯
原因: 使用 union 關(guān)鍵字時, 必須要保證兩張表的字段一模一樣(包括順序)
所以修改sql
sql _1 修改

select 
case 
bq.registration_type 
when 1 then '國有'
when 2 then '民營'
when 3 then '外資'
else ''
end as '注冊類型',
bqr.revenue as '營收',
'' as '總資產(chǎn)'
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 '總資產(chǎn)'
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 '總資產(chǎn)'
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 '總資產(chǎn)'
from bigdata_qiye bq 
left join bigdata_tech_improve_impl btii
on btii.qiye_id = bq.id)

查詢結(jié)果

在這里插入圖片描述