记录mysql问题

明天在用应用mysql group by 查问的时候呈现报错
SELECT    `id`,    `open_id`,    sum( `pay_amount` ) AS pay_amount,    sum( `pay_num` ) AS pay_num FROM    `table` WHERE    ( `app_id` = '30133' ) GROUP BY    `open_id` ORDER BY    `pay_amount` DESC     LIMIT 10

查问会报出SELECT list is not in GROUP BY clause and contains...
剖析得出

  • mysql5.7之后数据库默认模式改为 only_full_group_by模式，在执行SQL外面有些反复行group by的时候mysql 不晓得抉择哪一行。

解决方案：

  • 应用any_value()包含具体提醒的列名，使mysql不再纠结于具体哪一列
  • 优化SQL，对于每一个分组的元素 清晰的告知mysql 要如何抉择
#这样就okSELECT  ANY_VALUE(id) AS `id`,    `open_id`,    sum( `pay_amount` ) AS pay_amount,    sum( `pay_num` ) AS pay_num FROM    `table` WHERE    ( `app_id` = '30133' ) GROUP BY    `open_id` ORDER BY    `pay_amount` DESC     LIMIT 10