多重复匹配场景下GSCC表Comments列更新SQL优化需求

我在更新gab_source_cause_codes（简称gscc）表的Comments列时遇到问题：当Job、Suffix和Seq字段值重复时，现有的UPDATE语句只能匹配job_dtl_notes表中的第一行数据，无法将对应注释正确更新到gscc表的对应行。需要调整语句实现预期效果。

注：job_dtl_notes表的Date字段为char类型，gscc表的Date字段为datetime类型。

原更新语句 #

update gab_source_cause_codes 
   set comments = job_dtl_notes.text from job_dtl_notes where job_dtl_notes.job = gab_source_cause_codes.job and job_dtl_notes.suffix = gab_source_cause_codes.suffix and job_dtl_notes.job_seq = gab_source_cause_codes.seq and job_dtl_notes.date > '221231'  

job_dtl_notes表数据 #

当前更新后GSCC表数据 #

预期更新结果 #

解决方案 #

问题核心是原语句没有针对同一Job、Suffix、Seq下的多行数据做精准匹配，需要给两边的行添加分组序号，再基于序号关联更新。

完整更新语句 #

WITH ranked_notes AS (
    SELECT 
        Job, Suffix, Job_Seq, Date, Text,
        ROW_NUMBER() OVER (PARTITION BY Job, Suffix, Job_Seq, Date ORDER BY Text) AS row_num
    FROM job_dtl_notes
    WHERE Date > '221231'
),
ranked_gscc AS (
    SELECT 
        Job, Suffix, Seq, Date, Comments,
        ROW_NUMBER() OVER (PARTITION BY Job, Suffix, Seq, CAST(Date AS DATE) ORDER BY Date DESC) AS row_num
    FROM gab_source_cause_codes
)
UPDATE ranked_gscc
SET Comments = ranked_notes.Text
FROM ranked_gscc
JOIN ranked_notes ON 
    ranked_gscc.Job = ranked_notes.Job
    AND ranked_gscc.Suffix = ranked_notes.Suffix
    AND ranked_gscc.Seq = ranked_notes.Job_Seq
    AND CAST(ranked_gscc.Date AS DATE) = CONVERT(DATE, ranked_notes.Date, 112)
    AND ranked_gscc.row_num = ranked_notes.row_num;

关键说明 #

1. 分组编号：用ROW_NUMBER()给两组数据中，同一Job、Suffix、Seq/Job_Seq、日期的行分配唯一序号，确保每行能精准对应。
2. 日期转换：将gscc的datetime类型日期转成date，将job_dtl_notes的char类型日期（yyMMdd格式）用CONVERT(DATE, ..., 112)转成date类型，消除类型差异实现日期匹配。
3. 排序规则：gscc的行按Date DESC排序，匹配预期结果中同一日期下较晚的行对应wrong motor、较早的行对应dirty的规则，若需要调整对应顺序，修改ORDER BY的方向即可。

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