合并同字段表并避免ID重复，查询结果去重问题求助

嘿，我看你遇到了合并两张表时出现重复行和ID异常的问题，咱们来一步步把它搞定！

首先得分析下你当前SQL的问题：

• 你用LEFT JOIN + RIGHT JOIN + UNION的方式尝试实现全外连接，但这种方式没处理好同product+vint组合下的多对多关系，导致出现了多余的重复行（比如你看到的Pro_1 | s12 | 1 | NULL这条无效记录）。
• 另外，你的SQL里用了GROUP BY却没配合聚合函数，这会让数据库随机返回分组内的某一行，结果完全不可控，这也是错误ID出现的原因之一。

正确解决方案：窗口函数+全外连接实现精准配对 #

核心思路是给每个表中相同product+vint的记录分配唯一行号，然后基于行号做一对一的全外连接，这样就能保证每个bid_id和offer_id只出现一次，没有匹配的记录自动填充NULL。

支持FULL OUTER JOIN的数据库（如PostgreSQL、SQL Server）

WITH ranked_bids AS (
    SELECT 
        product, 
        vint, 
        id AS bid_id,
        -- 给同product+vint的bid按id排序分配行号
        ROW_NUMBER() OVER (PARTITION BY product, vint ORDER BY id) AS rn
    FROM bids
),
ranked_offers AS (
    SELECT 
        product, 
        vint, 
        id AS offer_id,
        -- 给同product+vint的offer按id排序分配行号
        ROW_NUMBER() OVER (PARTITION BY product, vint ORDER BY id) AS rn
    FROM offers
)
SELECT 
    -- 确保product和vint字段不为空
    COALESCE(rb.product, ro.product) AS product,
    COALESCE(rb.vint, ro.vint) AS vint,
    rb.bid_id,
    ro.offer_id
FROM ranked_bids rb
FULL OUTER JOIN ranked_offers ro
    ON rb.product = ro.product
    AND rb.vint = ro.vint
    AND rb.rn = ro.rn
ORDER BY product, vint;

不支持FULL OUTER JOIN的数据库（如MySQL）

可以用LEFT JOIN + UNION ALL模拟全外连接效果：

WITH ranked_bids AS (
    SELECT 
        product, 
        vint, 
        id AS bid_id,
        ROW_NUMBER() OVER (PARTITION BY product, vint ORDER BY id) AS rn
    FROM bids
),
ranked_offers AS (
    SELECT 
        product, 
        vint, 
        id AS offer_id,
        ROW_NUMBER() OVER (PARTITION BY product, vint ORDER BY id) AS rn
    FROM offers
)
-- 先获取所有bid的配对结果
SELECT 
    rb.product,
    rb.vint,
    rb.bid_id,
    ro.offer_id
FROM ranked_bids rb
LEFT JOIN ranked_offers ro
    ON rb.product = ro.product
    AND rb.vint = ro.vint
    AND rb.rn = ro.rn
UNION ALL
-- 再获取没有对应bid的offer记录
SELECT 
    ro.product,
    ro.vint,
    NULL AS bid_id,
    ro.offer_id
FROM ranked_offers ro
LEFT JOIN ranked_bids rb
    ON ro.product = rb.product
    AND ro.vint = rb.vint
    AND ro.rn = rb.rn
WHERE rb.bid_id IS NULL
ORDER BY product, vint;

方案说明 #

• 窗口函数ROW_NUMBER()会给每个product+vint组内的记录按id排序分配唯一行号rn，实现同组内bid和offer的精准一对一配对。
• 全外连接（或模拟方式）会保留两张表的所有记录，当某一侧没有匹配行时，对应的ID字段自动填充NULL。
• 最终结果不会有重复行，每个bid_id和offer_id仅出现一次，完全符合你的期望输出。

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