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

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行合并内容请搜索好吧啦网以前的文章或继续浏览下面的相关文章希望大家以后多多支持好吧啦网！