mysql group by 报错 GROUP BY clause and contains

记录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 要如何选择

#这样就ok
SELECT
  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