mysql - #1111 - 组函数 : MAX COUNT doesn't work with MYSQL5. 5 的使用无效

Question

我有一个表userInteret，我想选择用户之间的最大公共(public)interet。我试过这个查询:

SELECT nomInteretUser,idUser,COUNT(nomInteretUser) FROM `userInteret` 
WHERE nomInteretUser IN('piano','flute','chien') GROUP BY idUser

但是当我尝试这个时，我想要这个 COUNT 的最大值:

SELECT MAX(COUNT(nomInteretUser))FROM userInteret 
WHERE nomInteretUser IN('piano','flute','chien') GROUP BY idUser;

总是出错:

MySQL a répondu: Documentation

1111 - Invalid use of group function

Answer 1

SELECT idUser,COUNT(nomInteretUser) counts 
FROM userInteret
WHERE nomInteretUser IN('piano','flute','chien') 
GROUP BY idUser
ORDER BY counts DESC LIMIT 1