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]:语法错误或访问违规:1055 SELECT列表的表达式#1不在GROUP BY子句中，并且包含非聚合列'dbname.U。

在功能上不依赖于GROUP BY子句中的列

我不能改变我的组(错误的结果): group BY U.user_country_id, U.id

我怎样才能正确地改变我的请求?谢谢!

Answer 1

您可以通过这种方式获得最大(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)