在Sequelize中使用GROUP BY YEAR(created_at)报错，求解决方案

问题原因 #

你写的代码里，group: ["YEAR(created_at)"]会被Sequelize当作列名处理，自动给它加上反引号，生成的SQL变成GROUP BY YEAR(created_at)``，MySQL会把这个当作一个不存在的列，所以报错"Unknown column 'YEAR(created_at)' in 'group statement'"。

解决方案 #

方案一：使用sequelize.literal直接指定分组规则

把分组条件用literal包裹，避免Sequelize自动添加反引号：

const result = await arbcase.findAll({
  attributes: [
    [arbcase.sequelize.literal('YEAR(created_at)'), 'year'], // 可选，同时返回对应年份
    [arbcase.sequelize.literal('COUNT(*)'), 'count']
  ],
  group: [arbcase.sequelize.literal('YEAR(created_at)')]
});

方案二：用sequelize.fn定义年份别名，按别名分组

这种写法更符合Sequelize的ORM规范，同时能在结果中直接拿到对应年份：

const result = await arbcase.findAll({
  attributes: [
    // 调用MySQL的YEAR函数，给结果取别名year
    [arbcase.sequelize.fn('YEAR', arbcase.sequelize.col('created_at')), 'year'],
    // 统计每组数量
    [arbcase.sequelize.fn('COUNT', '*'), 'count']
  ],
  // 按别名year分组
  group: ['year']
});

方案二生成的正确SQL示例：

SELECT YEAR(`created_at`) AS `year`, COUNT(*) AS `count` FROM `arbcase` AS `arbcase` GROUP BY `year`;

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