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

admin Mysql

2023-12-01 0 233

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_member
FROM dw.fct_sales
where dimDateID between \’20170703\’ and \’20170709\’
and dimMemberID <>0
group by DATE_FORMAT(dimDateID,\’%W\’) ;
— 2、统计本周累计销售金额、订单量、会员数
SELECT SUM(AMT) as total_money
,COUNT(DISTINCT salesID) as total_num_order
,COUNT(DISTINCT dimMemberID) total_num_member
FROM dw.fct_sales
where 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_sales
where dimDateID between \’20170703\’ and \’20170709\’
and dimMemberID <>0),4)*100,\’%\’) as order_rate
FROM dw.fct_sales
where dimDateID between \’20170703\’ and \’20170709\’
and dimMemberID <>0
group 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_rate
FROM dw.fct_sales
where dimDateID between \’20170703\’ and \’20170709\’
and dimMemberID <>0
group 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_sales
where dimDateID between \’20170703\’ and \’20170709\’
and dimMemberID <>0
group 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_rate
FROM dw.fct_sales
where dimDateID between \’20170703\’ and \’20170709\’
and dimMemberID <>0
group 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_sales
where dimDateID between \’20170703\’ and \’20170709\’
and dimMemberID <>0
group by week(dimDateID,1); /*出现汇总函数需要进行分组*/
区分union 和 union all ，利用重复数据对比，合并两个一模一样的表
— 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\’)
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\’) ;
— 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
FROM dw.fct_sales
where dimDateID between \’20170703\’ and \’20170709\’
and dimMemberID <>0
group 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_member
FROM dw.fct_sales
where dimDateID between \’20170703\’ and \’20170709\’
and dimMemberID <>0
group 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 sn
order by money desc; /*注意要对money排序，而不是SUM(AMT)*/
— order by SUM(AMT) desc; /*会报错，需要用临时表的列名，并且要对临时表取列名，否则也会报错*/