关于mysql:mysql-group-by-报错-GROUP-BY-clause-and-contains

36次阅读

共计 570 个字符，预计需要花费 2 分钟才能阅读完成。

 明天在用应用 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

正文完