多同结构表重复行查询：三表场景下的SQL实现方案问询

要获取三张表中所有重复的user_id + order_id组合，并展示各表对应的create_time（无对应记录则为NULL），你需要先覆盖所有可能的跨表重复场景——原语句仅关联了orders_1和orders_2，因此漏掉了仅在orders_2和orders_3中存在的重复项（比如user_id=3, order_id=4）。

方法一：先筛选重复组合，再关联各表 #

这种方法逻辑清晰，先通过UNION ALL收集所有记录的user_id+order_id，再筛选出出现至少两次的重复组合，最后左连三张表提取对应时间：

SELECT
    combined.user_id,
    combined.order_id,
    o1.create_time AS create_time_1,
    o2.create_time AS create_time_2,
    o3.create_time AS create_time_3
FROM
    (
        SELECT user_id, order_id FROM dbo.orders_1
        UNION ALL
        SELECT user_id, order_id FROM dbo.orders_2
        UNION ALL
        SELECT user_id, order_id FROM dbo.orders_3
    ) AS combined
GROUP BY
    combined.user_id, combined.order_id
HAVING
    COUNT(*) >= 2 -- 仅保留重复出现的组合
LEFT JOIN dbo.orders_1 o1 ON combined.user_id = o1.user_id AND combined.order_id = o1.order_id
LEFT JOIN dbo.orders_2 o2 ON combined.user_id = o2.user_id AND combined.order_id = o2.order_id
LEFT JOIN dbo.orders_3 o3 ON combined.user_id = o3.user_id AND combined.order_id = o3.order_id
ORDER BY
    combined.user_id, combined.order_id;

方法二：用全连接覆盖所有跨表重复 #

如果不想用子查询分组，也可以通过多次FULL JOIN覆盖所有表间的重复场景，再过滤掉仅单表存在的记录：

SELECT
    COALESCE(o1.user_id, o2.user_id, o3.user_id) AS user_id,
    COALESCE(o1.order_id, o2.order_id, o3.order_id) AS order_id,
    o1.create_time AS create_time_1,
    o2.create_time AS create_time_2,
    o3.create_time AS create_time_3
FROM
    dbo.orders_1 o1
FULL JOIN dbo.orders_2 o2 ON o1.user_id = o2.user_id AND o1.order_id = o2.order_id
FULL JOIN dbo.orders_3 o3 ON 
    (o1.user_id = o3.user_id AND o1.order_id = o3.order_id) 
    OR (o2.user_id = o3.user_id AND o2.order_id = o3.order_id)
WHERE
    -- 过滤掉仅单表存在的记录
    (o1.user_id IS NOT NULL AND o2.user_id IS NOT NULL)
    OR (o1.user_id IS NOT NULL AND o3.user_id IS NOT NULL)
    OR (o2.user_id IS NOT NULL AND o3.user_id IS NOT NULL)
ORDER BY
    user_id, order_id;

结果验证 #

执行以上任一语句，都会得到你期望的输出：

| user_id | order_id | create_time_1           | create_time_2           | create_time_3           |
|---------|----------|-------------------------|-------------------------|-------------------------|
| 1       | 5        | 2018-05-04 02:26:03.808 | 2021-01-01 02:26:03.821 | NULL                    |
| 3       | 4        | NULL                    | 2021-12-12 02:26:03.814 | 2018-04-15 02:26:03.814 |

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