使用INSERT+SELECT+UNION为临时表生成连续行号的问题排查

问题描述 #

• 使用包含多UNION的SELECT语句向临时表##TAX_TEMP插入数据，需要基于FILE_NBR列生成全局连续的PAY_NBR，但目前每个UNION块内的PAY_NBR出现重复（错误结果见下表）。
• 编写的SQL语句触发“预期'as'或QUOTED_ID”语法错误。

错误SQL语句 #

INSERT INTO ##TAX_TEMP
    SELECT ROW_NUMBER() OVER (ORDER BY FILE_NBR) as 'PAY_NBR', * FROM (
                   SELECT PAYGROUP, BATCH_ID, FILE_NBR, ENTRY_NBR, PDE_TRANSTYPE
                   FROM TAX_TABLE
)

期望逻辑示例 #

SELECT PAYGROUP,
       BATCH_ID,
       FILE_NUM,
       ROW_NUMBER() OVER (ORDER BY FILE_NBR),
       ENTRY_NBR,
       PDE_TRANS_TYPE

错误结果表 #

期望结果表 #

解决方案 #

1. 修复语法错误 #

报错原因是子查询未指定别名，添加别名即可解决：

INSERT INTO ##TAX_TEMP
SELECT 
    ROW_NUMBER() OVER (ORDER BY FILE_NBR) AS PAY_NBR, 
    * 
FROM (
    SELECT PAYGROUP, BATCH_ID, FILE_NBR, ENTRY_NBR, PDE_TRANSTYPE
    FROM TAX_TABLE
) t -- 为子查询添加别名

2. 实现分组内全局连续行号（适配多UNION场景） #

如果查询包含多个UNION块，需将所有UNION结果作为整体，在外层统一生成行号，同时通过PARTITION BY FILE_NBR实现每个FILE_NBR分组内的连续编号：

INSERT INTO ##TAX_TEMP
SELECT 
    ROW_NUMBER() OVER (PARTITION BY FILE_NBR ORDER BY FILE_NBR) AS PAY_NBR,
    * 
FROM (
    -- 替换为你的多UNION查询逻辑
    SELECT PAYGROUP, BATCH_ID, FILE_NBR, ENTRY_NBR, PDE_TRANSTYPE FROM TAX_TABLE1
    UNION ALL
    SELECT PAYGROUP, BATCH_ID, FILE_NBR, ENTRY_NBR, PDE_TRANSTYPE FROM TAX_TABLE2
    UNION ALL
    SELECT PAYGROUP, BATCH_ID, FILE_NBR, ENTRY_NBR, PDE_TRANSTYPE FROM TAX_TABLE3
) t

• 若不需要按FILE_NBR分组，仅需全局所有行连续，去掉PARTITION BY FILE_NBR即可。

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