如何合并两个SQL表数据？按指定字段匹配展示对应记录

表结构 #

两个结构完全相同的SQL表table1和table2，创建语句如下：

create table table1 (
    header bit,
    [type] char(1),
    Intype varchar(3),
    agrid int,
    affid int,
    inno varchar(10),
    amount int
);

create table table2 (
    header bit,
    [type] char(1),
    Intype varchar(3),
    agrid int,
    affid int,
    inno varchar(10),
    amount int
);

场景1 #

测试数据插入语句 #

insert into table1 (header , [type] , Intype  ,agrid , affid, inno,amount)
values
(0, 'D','001',18,84,'001',null),
(0, 'N', '001', 18,84,'001',null);

insert into table2 (header , [type] , Intype  ,agrid , affid, inno,amount)
values
(1, null, null,18,84, '001', 90),
(1, null, null,18,84, '001', 60),
(1, null, null,18,84, '001', 84);

需求 #

对于table1中每一条header为0的记录，需展示与之关联的table2中header为1且inno、affid、agrid字段匹配的记录，保留原table1记录并按「原记录+对应关联记录」的顺序输出。

期望输出 #

header , [type] , Intype  ,agrid , affid, inno,amount
0      , 'D' , '001' , 18 , 84 , '001' , null     ----table 1 record for type D
1      , null, null, 18, 84, 001, 90          
1, null, null,18,84, 001, 60
1, null, null,18,84, 001, 84
0, 'N', '001', 18,84,'001',null             ----table 1 record for type N
1      , null, null, 18, 84, 001, 90          
1, null, null,18,84, 001, 60
1, null, null,18,84, 001, 84
0, 'N', '001', 18,84,'001',null

场景2 #

测试数据插入语句 #

insert into table1 (header , [type] , Intype  ,agrid , affid, inno,amount)
values
(0, 'D','001',14,95,'001',null),
(0, 'D', '001', 14,95,'008',null),
(0, 'N', '001', 14,95,'008',null);

insert into table2 (header , [type] , Intype  ,agrid , affid, inno,amount)
values
(1, null, null,14,95, '001', 11),
(1, null, null,14,95, '008', 23);

需求 #

同场景1逻辑：展示table1中每条header为0的记录，以及与之匹配的table2中header为1且inno、affid、agrid一致的记录，按「原记录+对应关联记录」的顺序输出。

期望输出 #

header , [type] , Intype  ,agrid , affid, inno,amount
0, 'D','001',14,95,'001',null    ----table 1 record for type D
1, null, null,14,95, 001, 11         
0, 'D', '001', 14,95,'008',null  ---table 1 record for type D
1, null, null,14,95, 008, 23
0, 'N', '001', 14,95,'008',null             ----table 1 record for type N
1, null, null,14,95, 008, 23

解决方案 #

要实现先输出table1记录、再紧跟对应table2匹配记录的效果，需通过分组标记和排序控制输出顺序，具体SQL语句如下：

WITH combined_data AS (
    -- 提取table1中header=0的记录，标记分组ID和排序优先级（1表示先输出）
    SELECT 
        header, [type], Intype, agrid, affid, inno, amount,
        ROW_NUMBER() OVER (ORDER BY [type], inno) AS group_id,
        1 AS sort_order
    FROM table1
    WHERE header = 0

    UNION ALL

    -- 提取table2中匹配的记录，关联table1的分组ID，标记排序优先级（2表示后输出）
    SELECT 
        t2.header, t2.[type], t2.Intype, t2.agrid, t2.affid, t2.inno, t2.amount,
        t1.group_id,
        2 AS sort_order
    FROM (
        SELECT 
            agrid, affid, inno,
            ROW_NUMBER() OVER (ORDER BY [type], inno) AS group_id
        FROM table1
        WHERE header = 0
    ) t1
    JOIN table2 t2 ON t1.agrid = t2.agrid 
                  AND t1.affid = t2.affid 
                  AND t1.inno = t2.inno
    WHERE t2.header = 1
)
SELECT header, [type], Intype, agrid, affid, inno, amount
FROM combined_data
ORDER BY group_id, sort_order;

逻辑说明 #

1. 用CTE合并两类数据：
   • 对table1的每条目标记录，生成唯一group_id用于分组，同时标记sort_order=1确保该记录先输出。
   • 关联table2时，通过agrid、affid、inno匹配对应分组，继承group_id并标记sort_order=2，确保在同组的table1记录之后输出。
2. 最终按group_id分组，再按sort_order排序，即可实现需求中的输出顺序。
3. 若需保留table1中的重复记录（如场景1末尾的重复N类型记录），可将ROW_NUMBER()替换为DENSE_RANK()，确保重复记录能各自带出对应的table2匹配记录。

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