SQL Server 2012按Id关联行数不同表不足补NULL的查询问题

问题原因 #

你之前的写法会出现问题，是因为直接按Id关联两个表时，同Id下Table1的每一行都会和Table2的所有行匹配，产生笛卡尔积，行数是同Id下两个表行数的乘积，完全不符合预期。

解决方案 #

我们可以先给两个表同Id分组内的每行加上独立的序号，再用Id+序号做全外连接，就能实现按行一一匹配、行数不足自动补NULL的效果，SQL Server 2012支持窗口函数ROW_NUMBER()，可以直接实现：

-- 给Table1同Id下的行按指定规则编序号
WITH T1_Ranked AS (
    SELECT 
        Id, Val1, Val2,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Val1, Val2) AS RowNum
    FROM @Table1
),
-- 给Table2同Id下的行按指定规则编序号
T2_Ranked AS (
    SELECT 
        Id, Val3, Val4,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Val3, Val4) AS RowNum
    FROM @Table2
),
-- 按Id+序号全连接两个表，实现行对齐
Combined AS (
    SELECT
        ISNULL(t1.Id, t2.Id) AS Id,
        t1.Val1, t1.Val2,
        t2.Val3, t2.Val4,
        ISNULL(t1.RowNum, t2.RowNum) AS RowNum
    FROM T1_Ranked t1
    FULL OUTER JOIN T2_Ranked t2
        ON t1.Id = t2.Id AND t1.RowNum = t2.RowNum
)
-- 关联主表取Name，按Id和序号排序得到最终结果
SELECT
    mt.Id,
    mt.Name,
    c.Val1,
    c.Val2,
    c.Val3,
    c.Val4
FROM @MainTable mt
INNER JOIN Combined c ON mt.Id = c.Id
ORDER BY mt.Id, c.RowNum

说明 #

如果需要调整两个表行的匹配顺序，修改ROW_NUMBER()中ORDER BY后的字段即可。另外你给出的预期输出里Id=3第一行的Val2值写为55属于笔误，实际运行代码后该值为45，和样例数据中的@Table1插入值一致。

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