使用LEFT JOIN关联两表返回重复错误数据的问题咨询

问题原因 #

当前的LEFT JOIN仅通过emp字段关联两张表，由于table1中tom有2条记录，table2中tom有3条记录，数据库会生成笛卡尔积（2×3=6条）——没有更精准的关联条件匹配每行数据的对应关系，这就是重复数据的来源。

正确实现方法 #

要得到预期的一一对应结果，需要给每张表中同一emp下的记录添加行号，再通过emp+行号进行关联，确保每行数据按顺序匹配。

具体SQL实现（适用于支持窗口函数的SQL方言，如MySQL 8.0+、PostgreSQL、SQL Server） #

SELECT 
    COALESCE(t1.emp, t2.emp) AS emp,
    t1.type,
    t2.grade
FROM (
    -- 给table1的记录按emp分组、type排序生成行号
    SELECT 
        emp, 
        type, 
        ROW_NUMBER() OVER(PARTITION BY emp ORDER BY type) AS rn 
    FROM table1
) t1
-- 用RIGHT JOIN保留table2的所有记录，对应行号无匹配时type为空
RIGHT JOIN (
    -- 给table2的记录按emp分组、grade排序生成行号
    SELECT 
        emp, 
        grade, 
        ROW_NUMBER() OVER(PARTITION BY emp ORDER BY grade) AS rn 
    FROM table2
) t2 
    ON t1.emp = t2.emp 
    AND t1.rn = t2.rn
ORDER BY t2.rn;

结果说明 #

• ROW_NUMBER()窗口函数会给每个emp分组内的记录，按指定顺序（type或grade）生成唯一行号
• 通过emp+rn关联，确保table1的第1行对应table2的第1行、第2行对应第2行
• RIGHT JOIN保证table2中多出来的第3条记录被保留，type字段自动为空

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