关于mysql: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