SQL 兩張表按分組序號橫向合併：ROW_NUMBER + UNION 實現不等長關聯

數據樣例

#table1: (id, value) → (1,22), (1,32), (2,22)
#table2: (id, value) → (1,55), (2,43), (2,63)

期望結果

按 id 分組，組內按順序編號，編號相同的橫向拼接；不足方以 NULL 補齊。

解決方案

SELECT t1.value v1, t1.value2 v2, t2.value v3, t2.value2 v4
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY value) rn FROM #table1
) t1
LEFT JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY value) rn FROM #table2
) t2 ON t1.rn = t2.rn AND t1.id = t2.id

UNION

SELECT t1.value v1, t1.value2 v2, t2.value v3, t2.value2 v4
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY value) rn FROM #table2
) t2
LEFT JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY value) rn FROM #table1
) t1 ON t1.rn = t2.rn AND t1.id = t2.id;

關鍵點

• 使用 ROW_NUMBER() OVER(PARTITION BY id ORDER BY value) 生成組內序號
• 雙向 LEFT JOIN + UNION 保證兩表數據全保留
• 適用於 SQL Server、PostgreSQL、Oracle 等支持窗口函數的數據庫