如何实现新老APP表单条匹配查询？排查现有SQL问题

原SQL的错误点 #

1. 语法错误：

   • CTE定义时，cte2, select 应为 cte2 as (select，缺少关键字as和包裹语句的括号
   • select * row_number() 缺少逗号，正确写法是select *, row_number()...
   • order by left(ActivityID,6) rn 缺少括号，row_number()函数的over子句未闭合，且行号别名rn的位置错误

2. 逻辑错误：

   • OldAPP的CTE中，partition by ActivityID 无意义，因为Old表的ActivityID都是唯一值，分区后每条记录的行号rn都会是1，无法区分同前缀的不同Old记录
   • NewAPP的CTE中，partition by left(ActivityID,6) 会把所有同前缀的New记录分到同一分区，生成的rn会是1、2...，但你筛选cte2.rn=1会丢失同前缀的其他New记录，和“每条New记录匹配一条Old记录”的需求矛盾

修正后的SQL #

WITH Old_CTE AS (
    SELECT 
        *,
        -- 按Old表ActivityID的前6位分组，给每个分组内的记录编序号
        ROW_NUMBER() OVER(PARTITION BY LEFT(ActivityID, 6) ORDER BY ActivityID) AS rn
    FROM OldApp
),
New_CTE AS (
    SELECT 
        *,
        -- 按New表的ActivityID分组，给每个分组内的重复记录编序号
        ROW_NUMBER() OVER(PARTITION BY ActivityID ORDER BY CreatedOn) AS rn
    FROM NewApp
)
-- 关联时用前缀匹配+序号匹配，让每条New记录对应同前缀下的一条Old记录
SELECT *
FROM New_CTE
LEFT JOIN Old_CTE 
    ON LEFT(Old_CTE.ActivityID, 6) = New_CTE.ActivityID 
    AND Old_CTE.rn = New_CTE.rn;

说明 #

• Old_CTE：把同前缀（前6位）的Old记录归为一组，每组内按ActivityID排序并编号，这样每个前缀下的Old记录会有1、2、3...的序号
• New_CTE：把New表中相同ActivityID的记录归为一组，按CreatedOn排序并编号（也可替换为其他排序字段），生成1、2...的序号
• 关联时通过前缀匹配+序号匹配，让New表中同ActivityID的第1条记录匹配Old表同前缀的第1条，第2条匹配第2条，以此实现每条New记录仅匹配一条Old记录

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