Mysql中使用Union—多表合并之行合并
更新時(shí)間：2023年07月03日 11:20:51 作者：榴蓮小冰棍
這篇文章主要介紹了Mysql中使用Union—多表合并之行合并,如果我們需要將兩個(gè)select語句的結(jié)果作為一個(gè)整體顯示出來，我們就需要用到union或者union all關(guān)鍵字,union(或稱為聯(lián)合)的作用是將多個(gè)結(jié)果合并在一起顯示出來,需要的朋友可以參考下
Union (all)語句格式
select 列名 from 表A
union （all）
select 列名 from 表B
注意事項(xiàng)：
- 合并的表的列數(shù)必須是一致的，也就是表的列數(shù)要相同
- union 與union all 對(duì)兩個(gè)查詢結(jié)果合并的時(shí)候，如果結(jié)果有重復(fù)，union 會(huì)把重復(fù)的值刪除；
- union 與union all不能與order by同時(shí)使用，如果要對(duì)結(jié)果進(jìn)行排序，可以用子查詢
案例解析
統(tǒng)計(jì)20170703—20170709周內(nèi)每天及本周累計(jì)銷售金額、訂單量、會(huì)員數(shù)、訂單占比
- 統(tǒng)計(jì)時(shí)間段內(nèi)每天的累計(jì)銷售金額、訂單量、會(huì)員數(shù)
- 統(tǒng)計(jì)本周累計(jì)銷售金額、訂單量、會(huì)員數(shù)
- 訂單占比
- union合并表
- 對(duì)比 union all合并表——因?yàn)闆]有重復(fù)數(shù)據(jù)，所以兩表合并結(jié)果一樣
-- 1、統(tǒng)計(jì)時(shí)間段內(nèi)每天的累計(jì)銷售金額、訂單量、會(huì)員數(shù)
SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把時(shí)間轉(zhuǎn)化為星期模式*/
,SUM(AMT) as money
,COUNT(DISTINCT salesID) as num_order /*一個(gè)會(huì)員一周可以下多單，所以要統(tǒng)計(jì)會(huì)員數(shù)，需要去重*/
,COUNT(DISTINCT dimMemberID) num_member
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by DATE_FORMAT(dimDateID,'%W') ;
-- 2、統(tǒng)計(jì)本周累計(jì)銷售金額、訂單量、會(huì)員數(shù)
SELECT SUM(AMT) as total_money
,COUNT(DISTINCT salesID) as total_num_order
,COUNT(DISTINCT dimMemberID) total_num_member
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0;
-- 3、訂單占比
SELECT DATE_FORMAT(dimDateID,'%W') AS week_1
,CONCAT(ROUND( COUNT(DISTINCT salesID)/(SELECT COUNT(DISTINCT salesID)
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0),4)*100,'%') as order_rate
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by DATE_FORMAT(dimDateID,'%W') ;
-- 4、union合并表
SELECT DATE_FORMAT(dimDateID,'%W') AS week_1
,SUM(AMT) as money
,COUNT(DISTINCT salesID) as num_order
,COUNT(DISTINCT dimMemberID) num_member
,CONCAT(ROUND( COUNT(DISTINCT salesID)/(
SELECT COUNT(DISTINCT salesID)
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0),4)*100,'%') as order_rate
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by DATE_FORMAT(dimDateID,'%W')
UNION
SELECT week(dimDateID,1) /*為了保證列數(shù)一樣，week返回日期為一年中的第幾周 weel(date,1):從周一開始為第一天*/
,SUM(AMT) as total_money
,COUNT(DISTINCT salesID) as total_num_order
,COUNT(DISTINCT dimMemberID) total_num_member
,'100%' as total /*為確保列數(shù)一樣*/
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by week(dimDateID,1); /*出現(xiàn)匯總函數(shù)需要進(jìn)行分組*/
-- 5、對(duì)比 union all合并表——因?yàn)闆]有重復(fù)數(shù)據(jù)，所以兩表合并結(jié)果一樣
SELECT DATE_FORMAT(dimDateID,'%W') AS week_1
,SUM(AMT) as money
,COUNT(DISTINCT salesID) as num_order
,COUNT(DISTINCT dimMemberID) num_member
,CONCAT(ROUND( COUNT(DISTINCT salesID)/(
SELECT COUNT(DISTINCT salesID)
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0),4)*100,'%') as order_rate
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by DATE_FORMAT(dimDateID,'%W')
UNION ALL
SELECT week(dimDateID,1) /*為了保證列數(shù)一樣，week返回日期為一年中的第幾周 weel(date,1):從周一開始為第一天*/
,SUM(AMT) as total_money
,COUNT(DISTINCT salesID) as total_num_order
,COUNT(DISTINCT dimMemberID) total_num_member
,'100%' as total /*為確保列數(shù)一樣*/
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by week(dimDateID,1); /*出現(xiàn)匯總函數(shù)需要進(jìn)行分組*/區(qū)分union 和 union all ，利用重復(fù)數(shù)據(jù)對(duì)比，合并兩個(gè)一模一樣的表
-- union
SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把時(shí)間轉(zhuǎn)化為星期模式*/
,SUM(AMT) as money
,COUNT(DISTINCT salesID) as num_order /*一個(gè)會(huì)員一周可以下多單，所以要統(tǒng)計(jì)會(huì)員數(shù)，需要去重*/
,COUNT(DISTINCT dimMemberID) num_member
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by DATE_FORMAT(dimDateID,'%W')
UNION
SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把時(shí)間轉(zhuǎn)化為星期模式*/
,SUM(AMT) as money
,COUNT(DISTINCT salesID) as num_order /*一個(gè)會(huì)員一周可以下多單，所以要統(tǒng)計(jì)會(huì)員數(shù)，需要去重*/
,COUNT(DISTINCT dimMemberID) num_member
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by DATE_FORMAT(dimDateID,'%W') ;
-- union all
SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把時(shí)間轉(zhuǎn)化為星期模式*/
,SUM(AMT) as money
,COUNT(DISTINCT salesID) as num_order /*一個(gè)會(huì)員一周可以下多單，所以要統(tǒng)計(jì)會(huì)員數(shù)，需要去重*/
,COUNT(DISTINCT dimMemberID) num_member
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by DATE_FORMAT(dimDateID,'%W')
UNION ALL
SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把時(shí)間轉(zhuǎn)化為星期模式*/
,SUM(AMT) as money
,COUNT(DISTINCT salesID) as num_order /*一個(gè)會(huì)員一周可以下多單，所以要統(tǒng)計(jì)會(huì)員數(shù)，需要去重*/
,COUNT(DISTINCT dimMemberID) num_member
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by DATE_FORMAT(dimDateID,'%W') ;多表合并中的排序問題 order by——把合并后的表作為一個(gè)臨時(shí)表，再進(jìn)行排序注意臨時(shí)表需要命名
排序要對(duì)新的列名進(jìn)行排序
SELECT sn.*
FROM (
SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把時(shí)間轉(zhuǎn)化為星期模式*/
,SUM(AMT) as money
,COUNT(DISTINCT salesID) as num_order /*一個(gè)會(huì)員一周可以下多單，所以要統(tǒng)計(jì)會(huì)員數(shù)，需要去重*/
,COUNT(DISTINCT dimMemberID) num_member
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by DATE_FORMAT(dimDateID,'%W')
UNION
SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把時(shí)間轉(zhuǎn)化為星期模式*/
,SUM(AMT) as money
,COUNT(DISTINCT salesID) as num_order /*一個(gè)會(huì)員一周可以下多單，所以要統(tǒng)計(jì)會(huì)員數(shù)，需要去重*/
,COUNT(DISTINCT dimMemberID) num_member
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by DATE_FORMAT(dimDateID,'%W') ) as sn
order by money desc; /*注意要對(duì)money排序，而不是SUM(AMT)*/
-- order by SUM(AMT) desc; /*會(huì)報(bào)錯(cuò)，需要用臨時(shí)表的列名，并且要對(duì)臨時(shí)表取列名，否則也會(huì)報(bào)錯(cuò)*/到此這篇關(guān)于Mysql中使用Union—多表合并之行合并的文章就介紹到這了,更多相關(guān)Mysql使用Union行合并內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了Mysql NULL導(dǎo)致的神坑，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-09-09
本文主要是分析了一下在工作中寫的一個(gè)mysql的自定義函數(shù)，很簡單，僅僅是希望能對(duì)大家理解mysql自定義函數(shù)有所幫助。2014-09-09
通過DQL,我們可以從數(shù)據(jù)庫中檢索出所需的數(shù)據(jù),進(jìn)行各種復(fù)雜的數(shù)據(jù)分析和處理,本文將深入探討MySQL DQL的各個(gè)方面,幫助你全面掌握這一重要技能,感興趣的朋友跟隨小編一起看看吧2025-06-06
MySQL系列之開篇 MySQL關(guān)系型數(shù)據(jù)庫基礎(chǔ)概念
數(shù)據(jù)庫是指長期儲(chǔ)存在計(jì)算機(jī)中的有組織的、可共享的數(shù)據(jù)集合，數(shù)據(jù)具有三大基本特點(diǎn)，永久存儲(chǔ)，有組織，可共享，是數(shù)據(jù)庫系統(tǒng)的核心，本文給大家分享MySQL關(guān)系型數(shù)據(jù)庫基礎(chǔ)概念，需要的朋友參考下吧2021-07-07
這篇文章主要給大家介紹了關(guān)于MySQL視圖簡介及基本操作的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04
這篇文章主要介紹了詳解MySQL Workbench使用教程，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
MySQL中InnoDB存儲(chǔ)引擎的鎖的基本使用教程
這篇文章主要介紹了MySQL中InnoDB存儲(chǔ)引擎的鎖的基本概念,是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識(shí),需要的朋友可以參考下2015-11-11
這篇文章主要介紹了mybatis 模糊查詢的實(shí)現(xiàn)方法的相關(guān)資料,希望通過本文能幫助到大家，讓大家掌握這部分內(nèi)容，需要的朋友可以參考下2017-10-10
mysql提示got timeout reading communication packets的解決方法
今天發(fā)現(xiàn)mysql錯(cuò)誤日志中出現(xiàn)got timeout reading communication packets，一般情況因?yàn)椴糠猪撁嫔捎袉栴}導(dǎo)致，這里簡單分享一下解決方法2019-05-05
MySQL提供了各種特殊函數(shù),它們可以幫助管理者查詢復(fù)雜的數(shù)據(jù),資料庫包括字符串函數(shù),數(shù)學(xué)函數(shù),日期函數(shù),程序函數(shù)等,下面這篇文章主要給大家介紹了關(guān)于MySQL特殊函數(shù)使用技巧的相關(guān)資料,需要的朋友可以參考下2023-05-05