SQL Server按PLANT去重取最大ACUTAL_SHIP_DATE如何消除重复子查询

优化方案 #

你可以用**公共表表达式（CTE）**封装重复的子查询逻辑，一次定义多次引用，还可以进一步用窗口函数ROW_NUMBER()简化整个取最大日期行的逻辑，不用额外做关联匹配，代码更简洁：

方案1：仅用CTE消除重复子查询（和原有逻辑完全一致） #

WITH base_data AS (
    SELECT TOP 10
        P.SLOT_NUM,
        P.PLANT,
        P.CONSUMPTIONENDITEM,
        P.SALESORDERNUMBER,
        P.SOLINEITEM,
        T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE
    FROM SRTPROJECT P WITH (NOLOCK)
    INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) 
        ON P.SLOT_NUM = T.SLOT_NUM
        AND P.PLANT = T.PLANT
        AND P.CONSUMPTIONENDITEM = T.MAT_NUM
)
SELECT A.PLANT,
    A.SLOT_NUM,
    A.CONSUMPTIONENDITEM,
    A.SALESORDERNUMBER,
    A.SOLINEITEM,
    A.ACUTAL_SHIP_DATE
FROM base_data A
INNER JOIN (
    SELECT DI.PLANT, MAX(DI.ACUTAL_SHIP_DATE) AS ACUTAL_SHIP_DATE
    FROM base_data DI
    GROUP BY DI.PLANT
) B ON A.PLANT = B.PLANT AND A.ACUTAL_SHIP_DATE = B.ACUTAL_SHIP_DATE;

该方案完全保留原有执行逻辑，仅将重复的子查询抽为公共CTE base_data，仅定义一次即可在后续查询中多次引用。

方案2：用窗口函数进一步简化逻辑（更推荐） #

如果使用的是SQL Server、MySQL 8.0、PostgreSQL等支持窗口函数的主流数据库，可以直接用ROW_NUMBER()按PLANT分组、按ACUTAL_SHIP_DATE倒序排序，直接取每个分组排名第一的行即可，无需额外做关联匹配：

WITH base_data AS (
    SELECT TOP 10
        P.SLOT_NUM,
        P.PLANT,
        P.CONSUMPTIONENDITEM,
        P.SALESORDERNUMBER,
        P.SOLINEITEM,
        T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE,
        ROW_NUMBER() OVER(PARTITION BY P.PLANT ORDER BY T.SHIP_ACTUAL_DATE DESC) AS rn
    FROM SRTPROJECT P WITH (NOLOCK)
    INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) 
        ON P.SLOT_NUM = T.SLOT_NUM
        AND P.PLANT = T.PLANT
        AND P.CONSUMPTIONENDITEM = T.MAT_NUM
)
SELECT PLANT,
    SLOT_NUM,
    CONSUMPTIONENDITEM,
    SALESORDERNUMBER,
    SOLINEITEM,
    ACUTAL_SHIP_DATE
FROM base_data
WHERE rn = 1;

如果同一个PLANT下存在多个相同最大日期的行需要全部保留，把ROW_NUMBER()替换为RANK()即可。

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