记录 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