MySQL 5.7 - 分组依据没有工作

Question

我使用的是 MySQL 5.7，有一个查询如下:

SELECT 
    U.*,
    C.country,
    C.id AS country_id,
    C.iso
FROM 
    `users` AS U
LEFT JOIN
    `countries` AS C
ON 
    U.user_country_id = C.id
WHERE
    U.user_deleted = 0
GROUP BY
    U.user_country_id
HAVING 
    MAX(U.user_credits)

问题是:

Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dbname.U.id' which is not functionally dependent on columns in GROUP BY clause;

我无法将我的组(错误结果)更改为:GROUP BY U.user_country_id, U.id

如何以正确的方式更改我的请求？谢谢!

Answer 1

您可以通过这种方式获取具有 max(U.user_credits) 的行

SELECT 
    U.*,
    C.country,
    C.id AS country_id,
    C.iso
FROM 
    `users` AS U
LEFT JOIN
    `countries` AS C
ON 
    U.user_country_id = C.id
WHERE
   U.user_deleted = 0
AND   U.user_credits = ( select max(U.user_credits) from `users` as U)