MySQL 資料底部出現總計字樣 第二種辦法 縱向合并 20161103

標籤：rollup   span   mysql   代碼   http   image   png   mysq   money   

上次在部落格http://www.cnblogs.com/Mr-Cxy/p/5923375.html

我們使用了group by with rollup 函數 field自訂排序 來實現添加底部總計字樣，代碼很長，

再有就是我們使用縱向合并 union all 函數 ifnull sum(if())函數來實現縱向合并添加總計字樣

 

SELECT IFNULL(f.城市,"總計") AS 城市,f.7月金額,f.8月金額,f.9月金額FROM (    SELECT e.*    FROM (        SELECT b.城市,SUM(IF(b.年月=201607,b.金額,NULL)) AS 7月金額,SUM(IF(b.年月=201608,b.金額,NULL)) AS 8月金額,SUM(IF(b.年月=201609,b.金額,NULL)) AS 9月金額        FROM (         SELECT city AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金額         FROM test_a03order AS a         GROUP BY city,DATE_FORMAT(order_time,"%Y%m")        ) AS b        GROUP BY b.城市     ) AS e    UNION ALL(        SELECT SUM(IF(1=2,0,NULL)) AS 城市,SUM(IF(d.年月=201607,d.金額,NULL)) AS 7月金額,SUM(IF(d.年月=201608,d.金額,NULL)) AS 8月金額,SUM(IF(d.年月=201609,d.金額,NULL)) AS 9月金額        FROM (            SELECT city AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金額            FROM test_a03order AS c            GROUP BY city,DATE_FORMAT(order_time,"%Y%m")        ) AS d    ) ) AS f

 

MySQL 資料底部出現總計字樣 第二種辦法 縱向合并 20161103