SQL_MODE=mysql的only_full_group_by的非聚合列[duplicate]

Question

SQL在sql_mode=ONLY_FULL_GROUP_BY中遇到了一些问题，如何重写这个SQL？

SELECT
    s.id,
    s.users_id,
    COUNT(*) as attempt,
    s.time
    s.language,
    s.is_public,
    u.name,
    u.nick
FROM
    s
INNER JOIN users u ON u.id = s.users_id
WHERE s.sid = 10 AND s.result = 1
GROUP BY s.users_id
ORDER BY s.time

Answer 1

两个问题。其一,列列表中缺少逗号；其二,使用该设置,必须通过以下方式将所有非摘要列包含在组中：

SELECT
    s.id,
    s.users_id,
    COUNT(*) as attempt,
    s.time,
    s.language,
    s.is_public,
    u.name,
    u.nick
FROM
    s
INNER JOIN users u ON u.id = s.users_id
WHERE s.sid = 10 AND s.result = 1
GROUP BY s.id,
    s.users_id,
    s.time,
    s.language,
    s.is_public,
    u.name,
    u.nick
ORDER BY s.time