Union (all)語句格式

select 列名 from 表A

union （all）

select 列名 from 表B

注意事項：

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

案例解析

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

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

注意臨時表需要命名

排序要對新的列名進行排序

SELECT sn.*FROM ( SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把時間轉化為星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一個會員一周可以下多單，所以要統(tǒng)計會員數，需要去重*/ ,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 /*把時間轉化為星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一個會員一周可以下多單，所以要統(tǒng)計會員數，需要去重*/ ,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; /*會報錯，需要用臨時表的列名，并且要對臨時表取列名，否則也會報錯*/

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