Union (all)語句格式

select 列名 from 表A

union （all）

select 列名 from 表B

注意事項：

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

案例解析

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

統計時間段內每天的累計銷售金額、訂單量、會員數統計本周累計銷售金額、訂單量、會員數訂單占比union合并表對比 union all合并表——因為沒有重復數據，所以兩表合并結果一樣-- 1、統計時間段內每天的累計銷售金額、訂單量、會員數SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把時間轉化為星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一個會員一周可以下多單，所以要統計會員數，需要去重*/ ,COUNT(DISTINCT dimMemberID) num_memberFROM dw.fct_saleswhere dimDateID between '20170703' and '20170709'and dimMemberID <>0group by DATE_FORMAT(dimDateID,'%W') ;-- 2、統計本周累計銷售金額、訂單量、會員數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); /*出現匯總函數需要進行分組*/-- 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); /*出現匯總函數需要進行分組*/區分union 和 union all ，利用重復數據對比，合并兩個一模一樣的表 -- unionSELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把時間轉化為星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一個會員一周可以下多單，所以要統計會員數，需要去重*/ ,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 /*一個會員一周可以下多單，所以要統計會員數，需要去重*/ ,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 /*一個會員一周可以下多單，所以要統計會員數，需要去重*/ ,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 /*一個會員一周可以下多單，所以要統計會員數，需要去重*/ ,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 /*一個會員一周可以下多單，所以要統計會員數，需要去重*/ ,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 /*一個會員一周可以下多單，所以要統計會員數，需要去重*/ ,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行合并內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！