Azure Databricks下拆分表后无公共列拼接的问题求解

你之前的代码使用INNER JOIN关联两个添加了行号的子表，仅会返回行号同时存在于两个子表的记录，code=01共有4行、code=04仅有3行，所以最后两行code=01的记录会被过滤，导致数据丢失。

采用LEFT JOIN以code=01的子表作为左表进行关联，即可完整保留所有code=01的记录，不足的code=04字段自动补NULL：

WITH cte_code_01 AS (
    SELECT
        *,
        ROW_NUMBER() OVER(ORDER BY year, type, version) AS rn
    FROM 输入表名
    WHERE code = '01'
),
cte_code_04 AS (
    SELECT
        *,
        ROW_NUMBER() OVER(ORDER BY year, type, version) AS rn
    FROM 输入表名
    WHERE code = '04'
)
SELECT
    t1.version, t1.value, t1.code, t1.type, t1.year,
    t2.version, t2.value, t2.code, t2.type, t2.year
FROM cte_code_01 t1
LEFT JOIN cte_code_04 t2 ON t1.rn = t2.rn

如果需要调整两行的匹配顺序，只需修改ROW_NUMBER()中OVER子句的ORDER BY字段即可，该语法完全兼容Azure Databricks及SQL Server运行环境。

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