MySQL排序_20160926

標籤：

在工作中對資料進行排序也是最常用的,比如根據使用者的下單金額降序 或者對銷售業績進行降序排序 在考核員工KPI時候也經常用到

一、order  by 函數

order by 函數預設根據後面欄位升序，使用降序使用

order by 欄位名 desc寫法

計算各城市各產品3個月份分別的訂單額,以9月金額降序

1 SELECT city,productID,productname,SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201607,pay_money,NULL)) AS 7月金額,2 SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201608,pay_money,NULL)) AS 8月金額,3 SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201609,pay_money,NULL)) AS 9月金額4 FROM test_a03order5 GROUP BY city,productID6 ORDER BY SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201609,pay_money,NULL)) DESC

2、order by field（）函數 自訂排序

類似於excel中自訂排序一樣 mysql也支援根據欄位自訂排序

2.1SQL 讓城市以city_A,city_C,city_B,city_D,city_E 這樣的排序  

對比2.2SQL  2.1SQL field 函數裡面沒有把city_F放到自訂順序裡面 結果city_F預設置前 這個在以後with rollup函數會用到

1 SELECT city,productID,productname,SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201607,pay_money,NULL)) AS 7月金額,2 SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201608,pay_money,NULL)) AS 8月金額,3 SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201609,pay_money,NULL)) AS 9月金額4 FROM test_a03order5 GROUP BY city,productID6 ORDER BY FIELD(city,"city_A","city_C","city_B","city_D","city_E")

2.2 SQL  field 函數裡面把city_F放到自訂順序裡面 city_F置於最後

1 SELECT city,productID,productname,SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201607,pay_money,NULL)) AS 7月金額,2 SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201608,pay_money,NULL)) AS 8月金額,3 SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201609,pay_money,NULL)) AS 9月金額4 FROM test_a03order5 GROUP BY city,productID6 ORDER BY FIELD(city,"city_A","city_C","city_B","city_D","city_E","city_F")

 

 

MySQL排序_20160926