MySQL 兩張表資料合併的實現

2023-09-06 14:10:56

有一個需求, 需要從資料庫中匯出兩張表的資料到同一個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 資料合併內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL 兩張表資料合併的實現

熱門文章