如何查询用户详情及与当前会话用户的共同好友数量

我拥有两张表USERS_TABLE和TABLE_USERS_FRIENDS，希望展示USERS_TABLE中的所有用户，并基于当前会话ID，统计我与每位用户的共同好友数量。尝试了以下SQL语句，但未能成功运行。

用户表（USERS_TABLE） #

user_uuid   | user_name | user_gender 
----------------|-----------|---------------
001-e74-9a5-83  | Peter     | Male
002-eed-b4e-6b  | Devindra  | Male
003-b61-4df-be  | Peggy     | Female
004-f9b-9da-d1  | Lucy      | Female
005-gx1-6hz-5o  | Priya     | Female

用户好友表（TABLE_USERS_FRIENDS） #

owner_uuid  | friend_uuid 
----------------|-----------------
001-e74-9a5-83  | 003-b61-4df-be
002-eed-b4e-6b  | 003-b61-4df-be

尝试的查询语句 #

SET @session = "001-e74-9a5-83";

SELECT u.user_uuid, u.user_name, u.user_gender, COUNT(a.mutual_friend_uuid) AS mutual_friends

FROM TABLE_USERS u

JOIN(
    SELECT CASE 
    WHEN friend_uuid = u.user_uuid
        THEN owner_uuid 
    ELSE friend_uuid 
        END AS mutual_friend_uuid 
    FROM TABLE_USERS_FRIENDS 
        WHERE friend_uuid = u.user_uuid 
    OR owner_uuid = u.user_uuid
) a
JOIN( 
    SELECT CASE 
    WHEN friend_uuid = @session
        THEN owner_uuid 
    ELSE friend_uuid 
        END AS mutual_friend_uuid 
    FROM TABLE_USERS_FRIENDS 
        WHERE friend_uuid = @session 
    OR owner_uuid = @session
) b
ON b.mutual_friend_uuid = a.mutual_friend_uuid 

期望结果 #

user_uuid   | user_name | user_gender | mutual_friends
----------------|-----------|-------------|-----------------
002-eed-b4e-6b  | Devindra  | Male        | 1
003-b61-4df-be  | Peggy     | Female      | 0
004-f9b-9da-d1  | Lucy      | Female      | 0
005-gx1-6hz-5o  | Priya     | Female      | 0

问题分析与解决方案 #

原查询的核心问题在于：子查询a中直接引用了外部表u的字段u.user_uuid，这不符合SQL子查询的作用域规则；同时使用JOIN会过滤掉没有共同好友的用户，无法得到期望的0值结果。

以下是修正后的查询语句，使用CTE（公共表表达式）拆分逻辑，确保正确统计所有用户的共同好友数量：

SET @session = '001-e74-9a5-83';

-- 提取当前会话用户的所有好友列表
WITH my_friends AS (
    SELECT 
        CASE 
            WHEN owner_uuid = @session THEN friend_uuid 
            ELSE owner_uuid 
        END AS friend_uuid
    FROM TABLE_USERS_FRIENDS
    WHERE owner_uuid = @session OR friend_uuid = @session
),
-- 提取每个用户的所有好友列表
user_friends AS (
    SELECT 
        u.user_uuid,
        CASE 
            WHEN f.owner_uuid = u.user_uuid THEN f.friend_uuid 
            ELSE f.owner_uuid 
        END AS friend_uuid
    FROM TABLE_USERS u
    LEFT JOIN TABLE_USERS_FRIENDS f 
        ON u.user_uuid = f.owner_uuid OR u.user_uuid = f.friend_uuid
)

-- 关联用户表与好友列表，统计共同好友数量
SELECT 
    u.user_uuid,
    u.user_name,
    u.user_gender,
    COUNT(mf.friend_uuid) AS mutual_friends
FROM TABLE_USERS u
LEFT JOIN user_friends uf ON u.user_uuid = uf.user_uuid
LEFT JOIN my_friends mf ON uf.friend_uuid = mf.friend_uuid
WHERE u.user_uuid != @session -- 排除当前会话用户自身
GROUP BY u.user_uuid, u.user_name, u.user_gender
ORDER BY mutual_friends DESC, u.user_uuid;

逻辑说明 #

1. CTE my_friends：统一格式提取当前用户的所有好友，不管用户在好友关系中是owner_uuid还是friend_uuid角色。
2. CTE user_friends：为每个用户提取他们的所有好友，同样统一格式。
3. 主查询：使用LEFT JOIN关联用户表、用户好友列表和当前用户好友列表，确保即使没有共同好友的用户也会被保留；通过COUNT(mf.friend_uuid)统计交集数量，最后排除当前用户自身，得到符合期望的结果。

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