MySQL-with rollup函數運用 _20160930

標籤：

在部落格裡http://www.cnblogs.com/Mr-Cxy/p/5898839.html提到了行轉列，

如果想在下面這個表下面添加一行 總計 資料行SQL代碼怎麼實現 並且根據9月金額進行城市降序 總計置於底部呢 

MySQL提供了 group by with rollup 函數進行group by 欄位的匯總

但是order by 互斥的不能同時用

第一步還是是先計算各城市每個月的金額

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 bGROUP BY b.城市

第二步我們先用group by with rollup 函數添加針對欄位的匯總

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 bGROUP BY b.城市 WITH ROLLUP

這個記錄沒有出現總計兩個字，怎麼實現呢 繼續修改代碼 ifnull()函數

第三 添加總計字樣(有坑) ifnull()函數是將空欄位另外命名

SELECT IFNULL(b.城市,"總計") AS 城市,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 bGROUP BY b.城市 WITH ROLLUP

第四 擺脫掉坑 

為什麼說有坑呢 如果ifnull()函數放在上面代碼位置 看似實現了總計的字樣  ifnull()是針對用了with rollup 函數總計這個位置出現空欄位時候修改它為總計字樣的,

如果城市這一列裡本身就含有沒有命名的城市呢 這一列就會出現多個總計字樣 因此我們需要將ifnull()函數放到嵌套的子表裡 將空欄位在子表裡面就預先改成別名（不能是總計字樣）

因此 使用2個ifnull()函數就不會有這樣的問題 結果是一樣的 

SELECT IFNULL(b.城市,"總計") AS 城市,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 IFNULL(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 bGROUP BY b.城市 WITH ROLLUP

  第五 排序 

在這篇部落格http://www.cnblogs.com/Mr-Cxy/p/5910291.html 提到了order by field()自訂排序函數 如紅框所示有這樣一個預設的自訂定序

因此 如果想實現 根據9月金額進行城市降序 總計置於底部 效果 可以把上面代碼當成一個子表嵌套 結合 order by field()自訂函數實現 

SELECT c.*FROM (    SELECT IFNULL(b.城市,"總計") AS 城市,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 IFNULL(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.城市 WITH ROLLUP) AS cORDER BY FIELD(城市,‘總計‘),c.9月金額 DESC

 

 

MySQL-with rollup函數運用 _20160930