MariaDB转PostgreSQL：SQL按月份名称分组统计的错误解决

错误原因 #

你遇到的报错源于PostgreSQL严格的GROUP BY规则：ORDER BY中使用的to_char(f.datetime, 'MM')既不在SELECT列表中，也未被包含在GROUP BY子句内，同时原始列f.datetime也未出现在GROUP BY或聚合函数中，违反了SQL分组逻辑要求。

解决方案1：同时按月份数字和名称分组 #

直接在GROUP BY中同时包含月份数字（用于排序）和月份名称（用于展示）——同一月份数字对应的名称唯一，不会破坏统计逻辑，还能保证排序正确：

select 
  trim(to_char(f.datetime, 'Month')) as month, 
  sum(case when f.amount >= 0 then f.amount else 0 end) as income, 
  sum(case when f.amount < 0 then f.amount else 0 end) as expense, 
  sum(f.amount) as difference 
from finance f
where f.country not in ('ZZ')
  and extract(year from f.datetime) = $year
GROUP BY to_char(f.datetime, 'MM'), to_char(f.datetime, 'Month')
order by to_char(f.datetime, 'MM')

注：trim()用于去除to_char('Month')返回结果的尾部空格（比如默认返回January ，trim后为January）

解决方案2：子查询预处理月份信息 #

先通过子查询提取每条记录的年份、月份数字、月份名称和金额，再在外层聚合统计，逻辑更清晰：

select
  month_name as month,
  sum(case when amount >= 0 then amount else 0 end) as income,
  sum(case when amount < 0 then amount else 0 end) as expense,
  sum(amount) as difference
from (
  select
    f.amount,
    extract(year from f.datetime) as year_num,
    to_char(f.datetime, 'MM') as month_num,
    trim(to_char(f.datetime, 'Month')) as month_name
  from finance f
  where f.country not in ('ZZ')
) t
where t.year_num = $year
group by t.month_num, t.month_name
order by t.month_num

关键说明 #

两种方案均通过月份数字（MM）排序，避免了月份名称按字母排序的错误（比如字母顺序中April会排在August前，数字排序则严格遵循1-12的顺序）。

内容的提问来源于stack exchange，提问作者Wouter