合并两张表的交易记录：出库入库数据匹配解决方案求助

核心思路 #

要解决两张表行数不一致的对齐问题，关键是给每张表的记录生成唯一行号，再通过FULL OUTER JOIN按行号关联，这样就能让数据少的表在对应行自动填充NULL，实现两边数据的逐行匹配。

具体SQL实现 #

方式一：子查询直接关联（轻量场景） #

SELECT 
    issue.IssueItem,
    issue.IssueQty,
    receive.ReceiveItem,
    receive.ReceiveQty
FROM (
    -- 给出库表添加行号
    SELECT 
        ItemName AS IssueItem,
        Qty AS IssueQty,
        ROW_NUMBER() OVER (ORDER BY ItemName) AS RowNum
    FROM table1
) issue
FULL OUTER JOIN (
    -- 给入库表添加行号
    SELECT 
        ItemName AS ReceiveItem,
        Qty AS ReceiveQty,
        ROW_NUMBER() OVER (ORDER BY ItemName) AS RowNum
    FROM table2
) receive ON issue.RowNum = receive.RowNum
-- 按行号排序，保证结果顺序符合预期
ORDER BY COALESCE(issue.RowNum, receive.RowNum);

方式二：临时表关联（大数据量场景） #

-- 创建出库临时表并生成行号
SELECT 
    ItemName AS IssueItem,
    Qty AS IssueQty,
    ROW_NUMBER() OVER (ORDER BY ItemName) AS RowNum
INTO #TempIssue
FROM table1;

-- 创建入库临时表并生成行号
SELECT 
    ItemName AS ReceiveItem,
    Qty AS ReceiveQty,
    ROW_NUMBER() OVER (ORDER BY ItemName) AS RowNum
INTO #TempReceive
FROM table2;

-- 全外连接临时表实现对齐
SELECT 
    ti.IssueItem,
    ti.IssueQty,
    tr.ReceiveItem,
    tr.ReceiveQty
FROM #TempIssue ti
FULL OUTER JOIN #TempReceive tr ON ti.RowNum = tr.RowNum
ORDER BY COALESCE(ti.RowNum, tr.RowNum);

-- 清理临时表
DROP TABLE #TempIssue;
DROP TABLE #TempReceive;

关键说明 #

• ROW_NUMBER()的ORDER BY字段可根据业务调整，比如按物料录入时间CreateTime排序，保证行号顺序符合业务逻辑
• COALESCE(issue.RowNum, receive.RowNum)用于排序时取存在的行号，避免因某张表行号缺失导致排序混乱
• FULL OUTER JOIN会保留两张表的所有记录，当其中一张表没有对应行号的记录时，对应字段显示NULL，完美覆盖你需要的两种场景

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