av一区二区在线观看_亚洲男人的天堂网站_日韩亚洲视频_在线成人免费_欧美日韩精品免费观看视频_久草视

您的位置:首頁技術文章
文章詳情頁

Mysql中使用Union—多表合并之行合并

瀏覽:27日期:2023-07-20 19:52:12

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行合并內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網!

標簽: MySQL 數據庫
相關文章:
主站蜘蛛池模板: 国产精品视频网 | 欧美在线一区二区三区 | 日本在线视频一区 | 日本理论片午伦夜理片在线观看 | 一区二区三区四区在线 | 91日韩欧美 | 黄色网在线 | 国产1区2区| 久久久成人免费视频 | 天天综合视频 | 精品国产久 | 超碰中文字幕 | 天天综合av | 欧美爱爱网站 | 四虎影院免费观看 | 五月婷婷丁香综合 | 他揉捏她两乳不停呻吟动态图 | 亚洲黄色小视频 | 国产精品网站在线观看 | a在线播放 | 亚洲在线免费观看 | av在线成人 | 一级片av | 亚洲欧美精品 | 亚洲深夜福利 | 国产成人在线免费观看 | 日韩专区在线观看 | 亚洲区一区二 | 日产av在线 | 欧美日韩综合在线 | 黄色大片免费观看 | 亚洲一区免费观看 | 福利在线看 | 四虎在线免费视频 | 午夜国产视频 | 狠狠干天天干 | 国精产品一区二区 | 最新国产在线视频 | 国产精品视频免费看 | 免费av网站在线观看 | 国产视频一区在线观看 |