mysql - 尝试构建一个 SQL SELECT 查询来显示出现次数最多的用户

Question

正如标题中提到的，我正在努力寻找一个 SQL 查询来显示所有用户的昵称(来自用户表)以及 3 个最常见的评级及其评级数量(按评级表中的出现次数排序)。

表格:

1. Users  
+========+==========+  
| userID | userName |  
+--------+----------+  
|   1    |  User1   |  
|   2    |  User2   |  
|   3    |  User3   |  
|   4    |  User4   |  
|   5    |  User5   |  
|   6    |  User6   |  
+========+==========+


2. Ratings (the rating column is irrelevant)  
+========+========+  
| userID | rating |  
+--------+--------+  
|   1    |   5    |  
|   3    |   4    |  
|   5    |   2    |  
|   1    |   5    |  
|   5    |   3    |  
|   4    |   6    |  
|   2    |   2    |  
|   1    |   4    |  
|   5    |   5    |  
|   2    |   1    |  
|   4    |   3    |  
|   5    |   1    |
+========+========+  

查询的结果将是:

User5 4  
User1 3  
User2 2  
User4 2  

这是迄今为止我的进度，尽管它显示没有值且未排序的用户，但可能不是正确的方式:

SELECT userName  
FROM Users  
WHERE userID  
IN(  
    SELECT userID s  
    FROM Ratings s  
    WHERE (  
        SELECT count( userID )  
        FROM Ratings  
        WHERE userID = s.userID  
        ) >= (  
            SELECT COUNT( userID ) AS number  
            FROM Ratings  
            GROUP BY userID  
            ORDER BY number DESC  
            LIMIT 1  
            OFFSET 3  
    )  
)   

如果有任何帮助，无论是完整的查询还是只是提示，我将不胜感激。预先感谢您!

Answer 1

SELECT Z.* FROM
  (
    SELECT u.UserName,
           COUNT( * ) AS NoOfOccurences
      FROM Users u
      JOIN 
           Ratings r
        ON u.userID = r.userID
     GROUP BY u.UserName
  ) Z
     ORDER BY Z.NoOfOccurences DESC;