如何将dibbs_nobid、dibbs_lost、dibbs_won三张表横向合并？

针对你需要将dibbs_nobid、dibbs_lost、dibbs_won三张结构一致的表按行号横向合并的需求，可以基于你现有的两张表合并逻辑扩展，核心是给每张表生成排序后的行号，再依次通过行号进行全外连接。

完整SQL代码 #

SELECT 
    dn.nsn_part_number AS nobid_nsn,
    dn.reason AS nobid_reason,
    dl.nsn_part_number AS lost_nsn,
    dl.reason AS lost_reason,
    dw.nsn_part_number AS won_nsn,
    dw.reason AS won_reason
FROM 
    (SELECT 
        *, 
        ROW_NUMBER() OVER (ORDER BY nsn_part_number) AS seqnum 
    FROM dibbs_nobid) dn
FULL OUTER JOIN 
    (SELECT 
        *, 
        ROW_NUMBER() OVER (ORDER BY nsn_part_number) AS seqnum 
    FROM dibbs_lost) dl ON dn.seqnum = dl.seqnum
FULL OUTER JOIN 
    (SELECT 
        *, 
        ROW_NUMBER() OVER (ORDER BY nsn_part_number) AS seqnum 
    FROM dibbs_won) dw ON COALESCE(dn.seqnum, dl.seqnum) = dw.seqnum;

逻辑说明 #

1. 生成行号：给每张表添加seqnum字段，按nsn_part_number排序后生成连续行号，确保每张表的行按指定顺序对应。
2. 全外连接：先将前两张表通过seqnum做全外连接，保留两张表的所有行；再将这个结果和第三张表做全外连接，用COALESCE(dn.seqnum, dl.seqnum)处理前两张表中某张表行号缺失的情况，保证第三张表的行能正确对应到已有行号。
3. 别名区分：给每个表的字段添加别名（如nobid_nsn），避免结果中字段名重复导致的冲突。

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