# Mr_Cxy

## MySQL排序_20160926

order by 函数默认根据后面字段升序，使用降序使用

order by 字段名 desc写法

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_a03order
5 GROUP BY city,productID
6 ORDER BY SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201609,pay_money,NULL)) DESC

2、order by field（）函数 自定义排序

2.1SQL 让城市以city_A,city_C,city_B,city_D,city_E 这样的排序

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_a03order
5 GROUP BY city,productID
6 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_a03order
5 GROUP BY city,productID
6 ORDER BY FIELD(city,"city_A","city_C","city_B","city_D","city_E","city_F")

posted on 2016-09-26 18:54  Mr_Cxy  阅读(565)  评论(0编辑  收藏  举报