mysql - 如何按年份和日期排序SQL结果？

我已经编写了PostgreSQL查询，它检索月份、年份和相应的计数。我的问题是：

SELECT
(CASE
WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 01 THEN 'January'
WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 02 THEN 'February'
WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 03 THEN 'March'
WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 04 THEN 'April'
WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 05 THEN 'May'
WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 06 THEN 'June'
WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 07 THEN 'July'
WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 08 THEN 'Augst'
WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 09 THEN 'September'
WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 10 THEN 'October'
WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 11 THEN 'November'
WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 12 THEN 'December'
END) as MONTH ,EXTRACT(YEAR FROM app_pgm_req_dt) as YEAR,count(*)
FROM app_pgm_choice GROUP BY MONTH,YEAR
order by YEAR

由于结果是按月份和年份排序的，所以它的订购月份按字母顺序排列，我希望它按实际月份顺序排列。
输出：

"September";1968;4
"September";1970;1
"Augst";1971;3
"February";1975;1
"February";1977;1
"May";1977;1
"September";1978;1
"February";1978;3
"April";1978;1
"September";1979;1
.....
.....

1978年的结果应该是2月，4月和9月
我该怎么做？

最佳答案

所以只需按月订购（'01'-'12'）：

SELECT CASE WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 01 THEN 'January'
         WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 02 THEN 'February'
         WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 03 THEN 'March'
         WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 04 THEN 'April'
         WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 05 THEN 'May'
         WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 06 THEN 'June'
         WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 07 THEN 'July'
         WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 08 THEN 'Augst'
         WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 09 THEN 'September'
         WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 10 THEN 'October'
         WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 11 THEN 'November'
         WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 12 THEN 'December'
       END AS MONTH,
       EXTRACT(YEAR FROM app_pgm_req_dt) AS YEAR,
       COUNT(*)
  FROM app_pgm_choice
 GROUP BY YEAR, MONTH, TO_CHAR (app_pgm_req_dt, 'MM')
 ORDER BY YEAR, TO_CHAR (app_pgm_req_dt, 'MM');

参见TO_CHAR文档。检查this demo。