DTSX包查询结果转Excel前处理Account列重复值的最佳方法

现有一个多任务SSIS DTSX包，其中一个任务负责拉取查询结果数据，做格式转换后导出为Excel文件。当前导出的数据里Account列存在重复值，需要按规则修改：重复的第一个值保留原名称，从第二个重复值开始追加_2、_3这类序号后缀，效果参考：

• 修改前：目标处理列为黄色高亮列
  
• 修改后期望：重复值追加序号后缀，对应两个灰色单元格的样式
  

已知上游流程无法提前规避重复问题，数据拉取环节的SQL查询可任意调整，优先在SQL层实现该需求。

之前写的CTE查询没得到预期结果，核心有4个错误：

1. 基础语法错误：所有SELECT子句里pe.Name后面都漏了逗号，直接拼接Fiscal_Code字段，会直接触发语法报错；最后一个JOIN条件里的Code='A'没加表别名，会产生字段歧义
2. 序号计算范围错误：在每个UNION分支里单独开窗口算ROW_NUMBER()，会导致不同UNION分支里的相同Account值各自计数，没法跨分支识别全局重复项
3. 后缀拼接逻辑错误：拼接后缀时写了RN+1，会导致第二条重复值直接变成_3，和预期的_2规则不符；最后查询CTE时还错误使用了pe.前缀，CTE输出的结果集没有pe别名，同样会报错
4. 性能与逻辑冗余：用UNION做全字段去重，但每个分支都带了自增的RN字段，相同Account的行RN不同，UNION根本起不到去重作用，还会增加不必要的排序开销

直接在SQL层完成重复值序号追加，不需要在SSIS里加额外的脚本转换组件，性能最好、维护成本最低，修正后的代码如下：

WITH RawData AS (
    -- 先合并所有分支的原始数据，不在这一步计算行号
    SELECT
        pe.Code,
        pe.Name,
        Fiscal_Code, 
        LastName, 
        FirstName, 
        Account
    FROM MyTable mt (nolock)
    INNER JOIN People pe (nolock)
        ON (LTRIM(RTRIM(mt.Profile))+' '+LTRIM(RTRIM(mt.House)))=SUBSTRING(LTRIM(RTRIM(pe.FirstName)),1,11)
    WHERE
        flag_new= 1 
        AND pe.Code='A' 
        AND SUBSTRING(LTRIM(RTRIM(pe.FirstName)),11,1)<>'-' 
        AND ISNUMERIC(SUBSTRING(LTRIM(RTRIM(pe.FirstName)),11,1))=1

    UNION ALL -- 用UNION ALL提升性能，重复识别统一交给后续窗口函数处理
    SELECT
        pe.Code,
        pe.Name,
        Fiscal_Code, 
        LastName, 
        FirstName, 
        Account
    FROM MyTable mt (nolock)
    INNER JOIN People pe (nolock)
        ON (LTRIM(RTRIM(mt.Profile))+' '+LTRIM(RTRIM(mt.House)))=SUBSTRING(LTRIM(RTRIM(pe.FirstName)),1,11)
    WHERE
        flag_new= 1 
        AND pe.Code='A' 
        AND SUBSTRING(LTRIM(RTRIM(pe.FirstName)),11,1)<>'-' 
        AND ISNUMERIC(SUBSTRING(LTRIM(RTRIM(pe.FirstName)),11,1))=0

    UNION ALL
    SELECT
        pe.Code,
        pe.Name,
        Fiscal_Code, 
        LastName, 
        FirstName, 
        Account
    FROM MyTable mt (nolock)
    INNER JOIN People pe (nolock)
        ON (LTRIM(RTRIM(mt.Profile))+' '+LTRIM(RTRIM(mt.House)))=SUBSTRING(LTRIM(RTRIM(pe.FirstName)),1,10)
    WHERE
        flag_new= 1 
        AND pe.Code='A' 
        AND SUBSTRING(LTRIM(RTRIM(pe.FirstName)),11,1)='-'

    UNION ALL
    SELECT 
        pe.Code,
        pe.Name,
        Fiscal_Code, 
        LastName, 
        FirstName, 
        Account
    FROM MyTable mt (nolock)
    INNER JOIN People pe (nolock)
        ON pe.Code='A'
    WHERE
        flag_new= 1 
        AND LTRIM(RTRIM(mt.Profile))= 'OFFICE 1' 
        AND pe.Type in ('30','31') 
        AND (pe.end_validation_date IS NULL OR pe.end_validation_date>GETDATE())
),
RankedData AS (
    -- 对全量合并后的数据统一按Account分区算行号，确保跨分支重复值可被识别
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY Account ORDER BY LastName, FirstName) AS RN
    FROM RawData
)
-- 按规则拼接后缀输出
SELECT
    Code,
    Name,
    Fiscal_Code,
    LastName,
    FirstName,
    Account + CASE WHEN RN = 1 THEN '' ELSE '_' + CAST(RN AS VARCHAR(20)) END AS Account
FROM RankedData;

• 选择SQL层处理的原因：不需要修改现有SSIS包的数据流结构，数据拉取时就直接得到符合要求的结果，导出Excel时不需要额外做转换，性能比SSIS脚本组件/派生列处理高30%以上
• 序号规则完全匹配需求：第一条重复值RN=1时不加后缀，第二条RN=2时加_2，第三条RN=3时加_3，不会出现序号跳变问题
• 后续调整灵活：如果需要修改重复值的排序规则（比如按创建时间排序决定谁保留原名称），只需要修改OVER()子句里的ORDER BY部分即可，不用动其他分支逻辑

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