PostgreSQL对同一张user_email_table查询获取所有用户两两关联结果

核心逻辑 #

通过表自关联实现用户两两配对，用大小比较条件排除自关联及重复配对项，直接输出要求字段即可。

完整SQL语句 #

WITH user_email_mapping AS (
    -- 先提取去重后的用户-邮箱映射，避免单用户多邮箱导致冗余数据
    SELECT DISTINCT user_id, email_id
    FROM user_email_table
)
SELECT
    ROW_NUMBER() OVER (ORDER BY u1.user_id, u2.user_id) AS id,
    u1.user_id AS user_id_1,
    u2.user_id AS user_id_2,
    u1.email_id AS user_id_1_email,
    u2.email_id AS user_id_2_email
FROM user_email_mapping u1
INNER JOIN user_email_mapping u2
    -- 此处用<仅生成单向不重复配对，如需双向配对（同时展示A-B、B-A）可改为!=
    ON u1.user_id < u2.user_id
ORDER BY u1.user_id, u2.user_id;

说明 #

• 如果业务场景中单个用户对应多个邮箱，可去掉CTE中的DISTINCT关键字，会生成所有邮箱组合的配对结果
• 输出的id字段用窗口函数自动生成连续序号，可根据业务需求调整生成规则

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