SQL 排序
2019-09-29 本文已影响0人
wpf_register
order by 函数
计算各城市各产品3个月份分别的订单额,以9月金额降序
SELECT city,productID,productname,
SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201607,pay_money,NULL)) AS 7月金额,
SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201608,pay_money,NULL)) AS 8月金额,
SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201609,pay_money,NULL)) AS 9月金额
FROM test_a03order
GROUP BY city,productID
ORDER BY SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201609,pay_money,NULL)) DESC
order by field（）函数 自定义排序
- 让城市以city_A,city_C,city_B,city_D,city_E 这样的排序
默认city_F 在最前排
SELECT city,productID,productname,
SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201607,pay_money,NULL)) AS 7月金额,
SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201608,pay_money,NULL)) AS 8月金额,
SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201609,pay_money,NULL)) AS 9月金额
FROM test_a03order
GROUP BY city,productID
ORDER BY FIELD(city,"city_A","city_C","city_B","city_D","city_E")
- order by field 函数里面把city_F放到自定义顺序里面
SELECT city,productID,productname,
SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201607,pay_money,NULL)) AS 7月金额,
SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201608,pay_money,NULL)) AS 8月金额,
SUM(IF(DATE_FORMAT(order_date,"%Y%m")=201609,pay_money,NULL)) AS 9月金额
FROM test_a03order
GROUP BY city,productID
ORDER BY FIELD(city,"city_A","city_C","city_B","city_D","city_E","city_F")
