SQL使用CTE标记重复值并在主查询引用报错求助

问题根因 #

你现在无法引用CTE的核心原因有4个：

• CTE必须和主查询写在同一个语句块里，不能单独定义完CTE之后分开写主查询
• 你给dupeinv CTE关联时指定了别名tci，但你在SELECT里写的是dupeinv.xxx，别名不匹配
• 关联dupeinv的条件写错了：你写的cl.r_idoc = ci.t_doc用的是原重复表ci的字段，而且关联键和CTE的主键t_idoc对不上
• 你写的concat函数缺了右括号，语法错误

最优实现方案 #

直接用去重后的CTE代替原来的重复表[ln106].[dbo].tcisli305100 ci，不用重复关联两次同个表，性能更好，修正后的完整语句如下：

WITH dupeinv AS (
    SELECT 
        t_idoc,
        t_idat,
        t_ccur -- 把你要用到的tcisli305100表的字段都放在CTE里，不用再关联原表
    FROM (
        SELECT
            tci.t_idoc,
            tci.t_idat,
            tci.t_ccur,
            ROW_NUMBER() OVER (PARTITION BY tci.t_idoc ORDER BY tci.t_idat DESC) AS rn
        FROM [ln106].[dbo].tcisli305100 tci
    ) AS t
    WHERE t.rn = 1
)
SELECT 
    'JCI' AS BU,
    RTRIM(LTRIM(cl.t_orno)) AS SALES_ORDER_NUMBER,
    cl.t_pono AS SALES_ORDER_LINE_NUMBER,
    CONCAT(cl.t_shpm, cl.t_pono, cl.t_idoc) AS SHIPPING_RECORD_ID,
    CASE 
        WHEN cl.t_dqua = 0 OR cl.t_dqua IS NULL THEN cl.t_amti 
        ELSE cl.t_amti / cl.t_dqua 
    END AS AR_INVOICE_LINE_ITEM_PRICE_LOCAL,
    cl.t_line AS AR_INVOICE_LINE_NUMBER,
    cl.t_dqua AS AR_INVOICE_LINE_ITEM_QUANTITY,
    CONCAT(tci.t_idoc,'|',FORMAT(tci.t_idat,'MMddyyyy')) AS 自定义字段名, -- 补全括号和别名，用CTE的别名tci引用
    tci.t_ccur AS AR_INVOICE_CURRENCY,
    tci.t_idat AS AR_INVOICE_DATE
FROM [ln106].[dbo].tcisli310100 cl
LEFT JOIN dupeinv tci ON cl.t_idoc = tci.t_idoc -- 直接关联去重后的CTE，删掉原来的重复表关联
LEFT JOIN t di ON cl.t_doc = di.t_doc -- 修正原语句遗漏的di表字段引用的点符号
LEFT JOIN (
    SELECT t_orno,t_pono 
    FROM [ln106].[dbo].ttdsls401100 
    WHERE t_oltp <> 1 
    GROUP BY t_orno,t_pono
) AS l ON l.t_orno=cl.t_orno COLLATE SQL_Latin1_General_CP1_CI_AS AND l.t_pono=cl.t_pono
WHERE tci.t_idat > '2017'

额外优化说明 #

• 原来你单独关联的重复表ci可以完全删掉，所有需要的字段从去重后的CTE取即可，避免重复扫描表
• CTE里不需要的字段不要查，减少数据加载消耗
• PARTITION BY已经按t_idoc分组，ORDER BY里不需要再写t_idoc，无实际意义还会增加排序消耗

内容的提问来源于stack exchange，提问作者U-HUE U-HUE