postgresql - 如何在没有 aphabetically 的情况下订购月份？

Question

帮我解决这个问题。我想在 PostgreSQL 上按字母顺序排列月份。

这是我的代码：

  SELECT to_char(closed_date,'Month') AS "month" , 
  count(card_number) AS "total"
  FROM c_card_status_history
  where extract(year from closed_date) = 2018
  GROUP BY "month" order by "month"

请帮我。谢谢

标签： postgresql

Answer 1

您需要将其保留closed_date为日期（或时间戳），以便您可以对其进行订购。为此，您可以分组date_trunc('month', closed_date)并将其用作表达式的顺序：

SELECT to_char(date_trunc('month', closed_date), 'Month') AS "month", 
       count(card_number) AS "total"
FROM c_card_status_history
where extract(year from closed_date) = 2018
GROUP BY date_trunc('month', closed_date)
ORDER BY date_trunc('month', closed_date)

grouping bydate_trunc('month', closed_date)将实现与按to_char()表达式分组相同的结果，因为您还将结果限制为仅从 2018 年开始的几个月。