快捷導(dǎo)航

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

更新時(shí)間：2023年07月03日 11:20:51 作者：榴蓮小冰棍

這篇文章主要介紹了Mysql中使用Union—多表合并之行合并,如果我們需要將兩個(gè)select語句的結(jié)果作為一個(gè)整體顯示出來，我們就需要用到union或者union all關(guān)鍵字,union(或稱為聯(lián)合)的作用是將多個(gè)結(jié)果合并在一起顯示出來,需要的朋友可以參考下

Union (all)語句格式

select 列名 from 表A

union （all）

select 列名 from 表B

注意事項(xiàng)：

合并的表的列數(shù)必須是一致的，也就是表的列數(shù)要相同
union 與union all 對兩個(gè)查詢結(jié)果合并的時(shí)候，如果結(jié)果有重復(fù)，union 會(huì)把重復(fù)的值刪除；
union 與union all不能與order by同時(shí)使用，如果要對結(jié)果進(jìn)行排序，可以用子查詢

案例解析

統(tǒng)計(jì)20170703—20170709周內(nèi)每天及本周累計(jì)銷售金額、訂單量、會(huì)員數(shù)、訂單占比

統(tǒng)計(jì)時(shí)間段內(nèi)每天的累計(jì)銷售金額、訂單量、會(huì)員數(shù)
統(tǒng)計(jì)本周累計(jì)銷售金額、訂單量、會(huì)員數(shù)
訂單占比
union合并表
對比 union all合并表——因?yàn)闆]有重復(fù)數(shù)據(jù)，所以兩表合并結(jié)果一樣

-- 1、統(tǒng)計(jì)時(shí)間段內(nèi)每天的累計(jì)銷售金額、訂單量、會(huì)員數(shù)
SELECT DATE_FORMAT(dimDateID,'%W') as week_1      /*把時(shí)間轉(zhuǎn)化為星期模式*/
      ,SUM(AMT) as money
      ,COUNT(DISTINCT salesID) as num_order       /*一個(gè)會(huì)員一周可以下多單，所以要統(tǒng)計(jì)會(huì)員數(shù)，需要去重*/
      ,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、統(tǒng)計(jì)本周累計(jì)銷售金額、訂單量、會(huì)員數(shù)
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)               /*為了保證列數(shù)一樣，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                 /*為確保列數(shù)一樣*/
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by week(dimDateID,1);            /*出現(xiàn)匯總函數(shù)需要進(jìn)行分組*/
-- 5、對比 union all合并表——因?yàn)闆]有重復(fù)數(shù)據(jù)，所以兩表合并結(jié)果一樣
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)               /*為了保證列數(shù)一樣，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                 /*為確保列數(shù)一樣*/
FROM dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID <>0
group by week(dimDateID,1);            /*出現(xiàn)匯總函數(shù)需要進(jìn)行分組*/

區(qū)分union 和 union all ，利用重復(fù)數(shù)據(jù)對比，合并兩個(gè)一模一樣的表

-- union
SELECT DATE_FORMAT(dimDateID,'%W') as week_1      /*把時(shí)間轉(zhuǎn)化為星期模式*/
      ,SUM(AMT) as money
      ,COUNT(DISTINCT salesID) as num_order       /*一個(gè)會(huì)員一周可以下多單，所以要統(tǒng)計(jì)會(huì)員數(shù)，需要去重*/
      ,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      /*把時(shí)間轉(zhuǎn)化為星期模式*/
      ,SUM(AMT) as money
      ,COUNT(DISTINCT salesID) as num_order       /*一個(gè)會(huì)員一周可以下多單，所以要統(tǒng)計(jì)會(huì)員數(shù)，需要去重*/
      ,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      /*把時(shí)間轉(zhuǎn)化為星期模式*/
      ,SUM(AMT) as money
      ,COUNT(DISTINCT salesID) as num_order       /*一個(gè)會(huì)員一周可以下多單，所以要統(tǒng)計(jì)會(huì)員數(shù)，需要去重*/
      ,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      /*把時(shí)間轉(zhuǎn)化為星期模式*/
      ,SUM(AMT) as money
      ,COUNT(DISTINCT salesID) as num_order       /*一個(gè)會(huì)員一周可以下多單，所以要統(tǒng)計(jì)會(huì)員數(shù)，需要去重*/
      ,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——把合并后的表作為一個(gè)臨時(shí)表，再進(jìn)行排序
注意臨時(shí)表需要命名

排序要對新的列名進(jìn)行排序

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

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

您可能感興趣的文章: