多表连接去重：user、profession、education三表关联查询求助

嘿，我来帮你解决这个表关联的重复问题！你之前用普通INNER JOIN得到大量重复数据，本质是因为同一个uid下的profession和education记录做了笛卡尔积——每个职业会和每个教育经历两两组合，自然就出现了冗余。而你想要的是让每个表的记录独立对应，无匹配项时显示null，这时候得用带行号的全外连接来实现。

解决思路 #

核心逻辑是给每个表中同uid的记录分配唯一行号，然后基于uid+行号做全外连接，从根源上避免不同行的职业、教育记录交叉匹配，消除重复。

具体SQL代码 #

WITH prof_with_row AS (
    -- 给每个uid下的职业记录分配行号
    SELECT uid, profName,
           ROW_NUMBER() OVER (PARTITION BY uid ORDER BY profName) AS row_num
    FROM profession
),
edu_with_row AS (
    -- 给每个uid下的教育记录分配行号
    SELECT uid, eduName,
           ROW_NUMBER() OVER (PARTITION BY uid ORDER BY eduName) AS row_num
    FROM education
),
user_with_row AS (
    -- 用户表每个uid仅一条记录，行号固定为1
    SELECT uid, uName,
           ROW_NUMBER() OVER (PARTITION BY uid ORDER BY uid) AS row_num
    FROM user
)
SELECT 
    u.uid, u.uName,
    p.uid, p.profName,
    e.uid, e.eduName
FROM user_with_row u
-- 全外连接职业表，同时匹配uid和行号
FULL OUTER JOIN prof_with_row p 
    ON u.uid = p.uid AND u.row_num = p.row_num
-- 全外连接教育表，用COALESCE处理可能的null值，匹配uid和行号
FULL OUTER JOIN edu_with_row e 
    ON COALESCE(u.uid, p.uid) = e.uid AND COALESCE(u.row_num, p.row_num) = e.row_num
-- 按uid和行号排序，保证结果顺序符合预期
ORDER BY COALESCE(u.uid, p.uid, e.uid), COALESCE(u.row_num, p.row_num, e.row_num);

代码说明 #

• 生成行号：用ROW_NUMBER()函数给每个uid下的多条记录分配递增行号，让同uid的不同职业/教育记录有唯一标识。
• 全外连接：确保三个表的所有记录都被包含，哪怕某个表没有对应uid或行号的记录，也会用null填充。
• 双重匹配条件：同时以uid和row_num作为连接条件，保证每个职业/教育记录只会和对应行号的用户记录匹配，不会产生交叉组合的重复。
• 排序处理：用COALESCE函数处理可能的null值，让结果按uid和行号有序排列，完全匹配你预期的输出格式。

执行这段SQL后，就能得到你想要的无重复、带null填充的结果啦！

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