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

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

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

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

鑒于是臨時(shí)的業(yè)務(wù)需求, 直接使用Navicat 進(jìn)行查詢并導(dǎo)出數(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 '注冊(cè)類(lèi)型（1.國(guó)有，2.民營(yíng)，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='申報(bào)企業(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è)擴(kuò)展信息',
  `revenue` double(16,2) DEFAULT NULL COMMENT '營(yíng)收',
  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è)申報(bào)信息表';

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è)擴(kuò)展信息',
  `total_input` decimal(64,2) DEFAULT NULL COMMENT '總投資',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='技改項(xiàng)目實(shí)施情況表';

需要合并導(dǎo)出 bigdata_qiye_report 表與 bigdata_tech_improve_impl 表的數(shù)據(jù)

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

希望導(dǎo)出的excel格式

在這里插入圖片描述

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

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

在這里插入圖片描述

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

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

第一個(gè)sql

select 
case 
bq.registration_type 
when 1 then '國(guó)有'
when 2 then '民營(yíng)'
when 3 then '外資'
else ''
end as '注冊(cè)類(lèi)型',
bqr.revenue as '營(yíng)收'
from bigdata_qiye bq 
left join bigdata_qiye_report bqr
on bqr.qiye_id = bq.id

第二個(gè)sql

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

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

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

查詢結(jié)果

在這里插入圖片描述