使用Sequelize结合Group与Inner Join时出现报错

嘿，这个问题我之前踩过坑！其实根源就是MySQL默认开启的only_full_group_by这个sql_mode在严格校验你的分组查询逻辑。

错误原因拆解 #

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ontro.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

翻译成人话就是：你用GROUP BY分组的时候，SELECT列表里的字段要么是GROUP BY里明确指定的列，要么是用聚合函数（比如COUNT()、MAX()、SUM()）处理过的结果。否则MySQL就会认为你的查询逻辑有问题——毕竟同一个分组下可能有多个不同的id值，数据库不知道该返回哪一个给你。

靠谱的解决思路 #

1. 调整Sequelize查询，符合SQL标准（最推荐）

根据你的业务需求，修改SELECT的字段或者GROUP BY的条件：

• 如果需要保留非分组字段，用聚合函数包裹它：

  const { sequelize, User } = require('./models');
  
  User.findAll({
    attributes: [
      'department', // 分组字段
      [sequelize.fn('COUNT', sequelize.col('id')), 'user_count'], // 聚合函数统计数量
      [sequelize.fn('MAX', sequelize.col('id')), 'latest_user_id'], // 取分组里最大的id
      [sequelize.fn('GROUP_CONCAT', sequelize.col('name')), 'all_user_names'] // 把分组里的名字拼起来
    ],
    group: ['department'] // 只按department分组
  });
  

• 如果业务需要分组后每个组的完整行数据，那得把所有SELECT的字段都加到GROUP BY里（但这样分组逻辑会变成多字段联合分组，得确认是否符合你的需求）：

  User.findAll({
    attributes: ['id', 'name', 'department'],
    group: ['department', 'id', 'name'] // 把所有SELECT的字段都加入分组
  });
  

2. 临时关闭MySQL的only_full_group_by模式（调试用，不推荐长期用）

如果你只是临时调试，不想改查询逻辑，可以在Sequelize的配置里修改sql_mode：

const sequelize = new Sequelize('your_db', 'your_user', 'your_pwd', {
  host: 'localhost',
  dialect: 'mysql',
  dialectOptions: {
    sqlMode: 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
  }
});

这里把默认sql_mode里的only_full_group_by去掉就行，但长期这么做可能会导致查询结果不一致，谨慎使用！

3. 用窗口函数实现“分组取特定行”（适合复杂业务场景）

如果你的需求是每个分组里取最新/最早的一条数据，那用窗口函数（比如ROW_NUMBER()）是更优雅的方式，Sequelize里可以这么写：

// 先写子查询，给每个分组的行加序号
const subQuery = sequelize.dialect.QueryGenerator.selectQuery('users', {
  attributes: [
    'id',
    'name',
    'department',
    [sequelize.literal('ROW_NUMBER() OVER (PARTITION BY department ORDER BY id DESC)'), 'row_num']
  ]
}).slice(0, -1); // 去掉Sequelize自动加的末尾分号

// 外层查询取每个分组里序号为1的行（也就是最新的那条）
User.findAll({
  attributes: ['id', 'name', 'department'],
  where: sequelize.literal(`(id, department) IN (SELECT id, department FROM (${subQuery}) AS temp WHERE row_num = 1)`)
});

总结 #

优先选第一种或第三种方案，毕竟符合SQL标准，能避免后续出现数据混乱的问题。第二种只是临时调试的权宜之计哦！

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