H2数据库：列“U”必须在GROUP BY列表中；SQL语句。

Question

H2数据库：列“U”必须在GROUP BY列表中；SQL语句。

3

我的本地查询长这样：

SELECT u
FROM (SELECT max(difficulty), u
      FROM (SELECT sum(difficulty) AS difficulty, username AS u
            FROM user_answer ua
                     JOIN question q ON ua.question_id = q.id
                     JOIN answer a ON ua.answer_id = a.id
                     JOIN user_quiz uq ON ua.user_quiz_id = uq.id
                     JOIN account ac ON uq.account_id = ac.id
            WHERE a.correct = 1
              AND uq.quiz_start_date_time >= (CURDATE() - INTERVAL 7 DAY)
            GROUP BY ua.user_quiz_id
            ORDER BY difficulty DESC) AS max_week) as d_u

在MySQL中一切正常。但是在H2数据库中出现了错误：

org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "U" must be in the GROUP BY list; SQL statement:
SELECT u FROM (SELECT max(difficulty), u FROM (SELECT sum(difficulty) AS difficulty, username AS u FROM user_answer ua JOIN question q ON ua.question_id = q.id JOIN answer a ON ua.answer_id = a.id JOIN user_quiz uq ON ua.user_quiz_id = uq.id JOIN account ac ON uq.account_id = ac.id WHERE a.correct = 1 AND uq.quiz_start_date_time >= (CURDATE() - INTERVAL 7 DAY) GROUP BY ua.user_quiz_id ORDER BY difficulty DESC) AS max_week) as d_u [90016-200]

如何解决它？

- Karol

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- nbk · Accepted Answer

只需在用户名周围添加聚合函数即可。

    SELECT u
    FROM (SELECT max(difficulty), u
          FROM (SELECT sum(difficulty) AS difficulty, MAX(username) AS u
                FROM user_answer ua
                         JOIN question q ON ua.question_id = q.id
                         JOIN answer a ON ua.answer_id = a.id
                         JOIN user_quiz uq ON ua.user_quiz_id = uq.id
                         JOIN account ac ON uq.account_id = ac.id
                WHERE a.correct = 1
                  AND uq.quiz_start_date_time >= (CURDATE() - INTERVAL 1 MONTH)
                GROUP BY ua.user_quiz_id
                ORDER BY difficulty DESC
            ) AS max_month
        GROUP BY u
        ) as d_u

当启用ONLY_FULL_GROUP_BY选项时，您需要使用聚合函数将所有列保留或将它们放在GROUP BY子句中。请参阅https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html。