mysql - SQL 按月份名称按时间顺序而不是按字母顺序排序

Question

我想让月份名称按时间顺序排列，而不是按字母顺序排列。这是我的 Sql 代码。

SELECT month, sum(total) 
FROM (SELECT MONTHNAME(terms) AS month, COUNT(DISTINCT project_num) AS total  
      FROM projects 
      WHERE terms >= '2017/01/01'
      GROUP BY MONTH(terms) 
      UNION 
      SELECT MONTHNAME(terms) AS month, COUNT(DISTINCT project_num) AS total 
      FROM archive
      WHERE terms >= '2017/01/01' 
      GROUP BY MONTH(terms) 
     ) AS test
GROUP BY month
ORDER BY month

上面代码的输出看起来像 This

我希望它是:

January

February

March

...

...

Answer 1

将月份作为 int 进行排序。 使用 MONTH(STR_TO_DATE(month, '%M'))

SELECT month, sum(total) 
    FROM (SELECT MONTHNAME(terms) AS month, COUNT(DISTINCT project_num) AS total  
          FROM projects 
          WHERE terms >= '2017/01/01'
          GROUP BY MONTH(terms) 
          UNION 
          SELECT MONTHNAME(terms) AS month, COUNT(DISTINCT project_num) AS total 
          FROM archive
          WHERE terms >= '2017/01/01' 
          GROUP BY MONTH(terms) 
         ) AS test
    GROUP BY month
    ORDER BY MONTH(STR_TO_DATE(month, '%M'))