Union (all)語句格式

select 列名 from 表A

union （all）

select 列名 from 表B

注意事項(xiàng)：

合并的表的列數(shù)必須是一致的，也就是表的列數(shù)要相同union 與union all 對兩個(gè)查詢結(jié)果合并的時(shí)候，如果結(jié)果有重復(fù)，union 會把重復(fù)的值刪除；union 與union all不能與order by同時(shí)使用，如果要對結(jié)果進(jìn)行排序，可以用子查詢

案例解析

統(tǒng)計(jì)20170703—20170709周內(nèi)每天及本周累計(jì)銷售金額、訂單量、會員數(shù)、訂單占比

統(tǒng)計(jì)時(shí)間段內(nèi)每天的累計(jì)銷售金額、訂單量、會員數(shù)統(tǒng)計(jì)本周累計(jì)銷售金額、訂單量、會員數(shù)訂單占比union合并表對比 union all合并表——因?yàn)闆]有重復(fù)數(shù)據(jù)，所以兩表合并結(jié)果一樣-- 1、統(tǒng)計(jì)時(shí)間段內(nèi)每天的累計(jì)銷售金額、訂單量、會員數(shù)SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把時(shí)間轉(zhuǎn)化為星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一個(gè)會員一周可以下多單，所以要統(tǒng)計(jì)會員數(shù)，需要去重*/ ,COUNT(DISTINCT dimMemberID) num_memberFROM dw.fct_saleswhere dimDateID between '20170703' and '20170709'and dimMemberID <>0group by DATE_FORMAT(dimDateID,'%W') ;-- 2、統(tǒng)計(jì)本周累計(jì)銷售金額、訂單量、會員數(shù)SELECT SUM(AMT) as total_money ,COUNT(DISTINCT salesID) as total_num_order ,COUNT(DISTINCT dimMemberID) total_num_memberFROM dw.fct_saleswhere 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_saleswhere dimDateID between '20170703' and '20170709'and dimMemberID <>0),4)*100,'%') as order_rateFROM dw.fct_saleswhere dimDateID between '20170703' and '20170709'and dimMemberID <>0group 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_rateFROM dw.fct_saleswhere dimDateID between '20170703' and '20170709'and dimMemberID <>0group 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_saleswhere dimDateID between '20170703' and '20170709'and dimMemberID <>0group by week(dimDateID,1); /*出現(xiàn)匯總函數(shù)需要進(jìn)行分組*/-- 5、對比 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_rateFROM dw.fct_saleswhere dimDateID between '20170703' and '20170709'and dimMemberID <>0group 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_saleswhere dimDateID between '20170703' and '20170709'and dimMemberID <>0group by week(dimDateID,1); /*出現(xiàn)匯總函數(shù)需要進(jìn)行分組*/區(qū)分union 和 union all ，利用重復(fù)數(shù)據(jù)對比，合并兩個(gè)一模一樣的表 -- unionSELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把時(shí)間轉(zhuǎn)化為星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一個(gè)會員一周可以下多單，所以要統(tǒng)計(jì)會員數(shù)，需要去重*/ ,COUNT(DISTINCT dimMemberID) num_memberFROM dw.fct_saleswhere dimDateID between '20170703' and '20170709'and dimMemberID <>0group 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è)會員一周可以下多單，所以要統(tǒng)計(jì)會員數(shù)，需要去重*/ ,COUNT(DISTINCT dimMemberID) num_memberFROM dw.fct_saleswhere dimDateID between '20170703' and '20170709'and dimMemberID <>0group by DATE_FORMAT(dimDateID,'%W') ;-- union allSELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把時(shí)間轉(zhuǎn)化為星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一個(gè)會員一周可以下多單，所以要統(tǒng)計(jì)會員數(shù)，需要去重*/ ,COUNT(DISTINCT dimMemberID) num_memberFROM dw.fct_saleswhere dimDateID between '20170703' and '20170709'and dimMemberID <>0group 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è)會員一周可以下多單，所以要統(tǒng)計(jì)會員數(shù)，需要去重*/ ,COUNT(DISTINCT dimMemberID) num_memberFROM dw.fct_saleswhere dimDateID between '20170703' and '20170709'and dimMemberID <>0group by DATE_FORMAT(dimDateID,'%W') ;多表合并中的排序問題 order by——把合并后的表作為一個(gè)臨時(shí)表，再進(jìn)行排序

注意臨時(shí)表需要命名

排序要對新的列名進(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è)會員一周可以下多單，所以要統(tǒng)計(jì)會員數(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è)會員一周可以下多單，所以要統(tǒng)計(jì)會員數(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 snorder by money desc; /*注意要對money排序，而不是SUM(AMT)*/-- order by SUM(AMT) desc; /*會報(bào)錯，需要用臨時(shí)表的列名，并且要對臨時(shí)表取列名，否則也會報(bào)錯*/

到此這篇關(guān)于Mysql中使用Union—多表合并之行合并的文章就介紹到這了,更多相關(guān)Mysql使用Union行合并內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！