PostgreSQL：如何查询拥有最多好友的用户？

首先，你的现有查询只是分别统计了用户作为User1和User2的好友数，但没有合并同一个用户的总好友数（比如一个用户既在User1列出现，也在User2列出现时，会被分成两行统计）。我们需要先统一所有用户的好友记录，再统计总好友数，最后筛选出好友数最多的用户。

步骤1：正确统计每个用户的总好友数 #

先通过UNION ALL将所有好友关系双向展开，确保每个用户的所有好友都被计入统计：

SELECT user_id, COUNT(friend_id) AS friend_count
FROM (
    -- 把每条好友记录拆成双向的用户-好友对
    SELECT User1 AS user_id, User2 AS friend_id FROM Friendship
    UNION ALL
    SELECT User2 AS user_id, User1 AS friend_id FROM Friendship
) AS all_friendships
GROUP BY user_id

步骤2：筛选出好友数最多的用户 #

以下提供几种常用方案：

方案一：使用子查询匹配最大值（兼容多数数据库）

SELECT user_id, friend_count
FROM (
    SELECT user_id, COUNT(friend_id) AS friend_count
    FROM (
        SELECT User1 AS user_id, User2 AS friend_id FROM Friendship
        UNION ALL
        SELECT User2 AS user_id, User1 AS friend_id FROM Friendship
    ) AS all_friendships
    GROUP BY user_id
) AS user_counts
WHERE friend_count = (
    SELECT MAX(friend_count)
    FROM (
        SELECT user_id, COUNT(friend_id) AS friend_count
        FROM (
            SELECT User1 AS user_id, User2 AS friend_id FROM Friendship
            UNION ALL
            SELECT User2 AS user_id, User1 AS friend_id FROM Friendship
        ) AS all_friendships
        GROUP BY user_id
    ) AS counts
)

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

这种方法更简洁，且能同时选出所有好友数并列最多的用户：

WITH user_friend_counts AS (
    SELECT 
        user_id, 
        COUNT(friend_id) AS friend_count,
        -- 按好友数降序排名，并列第一的用户排名都是1
        RANK() OVER (ORDER BY COUNT(friend_id) DESC) AS rank_num
    FROM (
        SELECT User1 AS user_id, User2 AS friend_id FROM Friendship
        UNION ALL
        SELECT User2 AS user_id, User1 AS friend_id FROM Friendship
    ) AS all_friendships
    GROUP BY user_id
)
SELECT user_id, friend_count
FROM user_friend_counts
WHERE rank_num = 1

说明 #

• 如果你的数据库不支持WITH（CTE语法），可以用方案一；如果支持窗口函数，方案二更高效易读。
• 两种方案都会处理多个用户好友数并列最多的情况，不会只返回一个用户。

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