mysql - 仅提取具有最高值的行

Question

我对 SQL 比较陌生，我正在尝试提取具有最高值的行。

例如，表格如下所示:

user_id  fruits
1        apple
1        orange
2        apple
1        pear

我想提取数据，使其看起来像这样:

user_id  fruits
1        3

如果 user_id 2 有 3 个水果，它应该显示:

user_id  fruits
1        3
2        3

如果我按 DESC 命令使用 LIMIT = 1，我只能设法获得，但这不是正确的方法。否则我只会得到:

user_id  fruits
1        3
2        1

不确定在哪里存储最大值以放入 where 子句。感谢任何帮助，谢谢

Answer 1

使用RANK():

WITH cte AS (
    SELECT user_id, COUNT(*) AS cnt, RANK() OVER (ORDER BY COUNT(*) DESC) rnk
    FROM yourTable
    GROUP BY user_id
)

SELECT user_id, cnt AS fruits
FROM cte
WHERE rnk = 1;