SQL新手求助：如何提取用户水果数量最大值对应的行记录

要提取水果记录数量最多的用户行，你可以通过子查询嵌套或者窗口函数两种方式实现，以下是具体操作：

方法一：子查询嵌套（兼容大部分SQL数据库） #

先统计每个用户的水果数量，再找出这些数量中的最大值，最后筛选出数量等于最大值的用户：

SELECT user_id, COUNT(fruits) AS fruits
FROM your_table_name  -- 替换成你的实际表名
GROUP BY user_id
HAVING COUNT(fruits) = (
    -- 内层子查询计算所有用户的水果数量，外层取最大值
    SELECT MAX(count_val)
    FROM (
        SELECT COUNT(fruits) AS count_val
        FROM your_table_name
        GROUP BY user_id
    ) AS user_counts
);

方法二：窗口函数（适用于支持窗口函数的数据库，如MySQL8+、PostgreSQL等） #

用窗口函数给用户的水果数量排名，直接取排名第一的记录（多个用户数量相同时会全部返回）：

-- 先统计每个用户的水果数量
WITH user_fruit_counts AS (
    SELECT user_id, COUNT(fruits) AS fruits
    FROM your_table_name
    GROUP BY user_id
),
-- 给数量降序排名，相同数量的用户排名一致
ranked_counts AS (
    SELECT user_id, fruits,
           RANK() OVER (ORDER BY fruits DESC) AS rnk
    FROM user_fruit_counts
)
-- 提取排名第一的记录
SELECT user_id, fruits
FROM ranked_counts
WHERE rnk = 1;

效果验证 #

• 仅user_id=1有3条记录时，返回结果：

user_id  fruits
1        3

• user_id=1和2都有3条记录时，返回结果：

user_id  fruits
1        3
2        3

内容的提问来源于stack exchange，提问作者thompsonrapier