如何提取特定组合下含最大日期的SQL数据行？

问题背景 #

需要从全量计算数据中，仅保留配方编号、物料编号、计算位置、容器编号组合下的最新（最大计算日期）数据行。基础查询可返回全量结果，但全量数据量过大（超91000行），需精简至约20000行的有效数据。

示例场景：配方编号20091全量返回704行（32次历史计算×22个计算位置），需筛选为44行——对应2个容器编号，每个容器取最新一次计算的22个位置记录。

解决方案：使用ROW_NUMBER()窗口函数 #

将原查询逻辑包裹在CTE中，通过窗口函数按目标组合分组并标记最新记录，最终筛选出标记为1的行：

WITH RankedCalculations AS (
    SELECT 
        T1."NUMMER" "Rezeptnummer",
        T1."BEZEICHNUNG1" "Rezeptbezeichnung",
        T3."SUMMEMATERIALWERTL" "Rezept Materialwert L",
        T15."NUMMER" "Kalkulationsschema-Nr",
        T15."BEZEICHNUNG" "Kalkulationsschema-Bez",
        T13."KALKULATIONSDATUM" "Kalkulationsdatum",
        T14."POSNR" "Kalkulationsposition",
        T14."BEZEICHNUNG" "Kalkulationsposition-Bez",
        T14."VOLLKOSTENHUNDERTME" "Vollkosten Hundert ME",
        T14."BASISBEWERTUNGSKOSTEN" "Basis Bewertungskosten Proz.",
        T14."BASISVOLLKOSTEN" "Basis Vollkosten Proz.",
        T17."NUMMER" "Artikelummer",
        T17."BEZEICHNUNG1" "Artikelbezeichnung",
        T19."EMBALLAGENNUMMER" "Emballagennummer",
        T19."EMBALLAGENBEZEICHNUNG" "Emballagenbezeichnung",
        -- 按目标组合分组，给最新日期的行标记为1
        ROW_NUMBER() OVER (
            PARTITION BY 
                T1."NUMMER",          -- 配方编号
                T17."NUMMER",         -- 物料编号
                T19."EMBALLAGENNUMMER", -- 容器编号
                T14."POSNR"           -- 计算位置
            ORDER BY T13."KALKULATIONSDATUM" DESC
        ) AS RowRank
    FROM 
         dibac.CORE_EMBALLAGE T21
         RIGHT OUTER JOIN dibac.CORE_ARTIKELGEBINDE T19 ON T21.INTID = T19.INTID_EMBALLAGE
         LEFT OUTER JOIN dibac.CORE_ARTIKEL T17 ON T19.INTID_ARTIKEL = T17.INTID
         RIGHT OUTER JOIN dibac.CORE_VORKALKULATION T13 ON T17.INTID = T13.INTID_ARTIKEL
         LEFT OUTER JOIN dibac.CORE_REZEPT T1 ON T13.INTID_REZEPT = T1.INTID
         LEFT OUTER JOIN dibac.T_MD_STATE T2 ON T1.STRUID_NUCLOSSTATE = T2.STRUID
         LEFT OUTER JOIN dibac.CORE_REZEPTPOSITION T3 ON T1.INTID = T3.INTID_REZEPT
         LEFT OUTER JOIN dibac.CORE_VORKALKULATIONPOSITION T14 ON T13.INTID = T14.INTID_VORKALKULATION
         LEFT OUTER JOIN dibac.CORE_PARAMETERDATENQUELLE T20 ON T14.INTID_DATENQUELLE = T20.INTID
         LEFT OUTER JOIN dibac.CORE_KALKULATIONSSCHEMA T15 ON T13.INTID_KALKULATIONSSCHEMA = T15.INTID
         LEFT OUTER JOIN dibac.T_MD_STATE T18 ON T17.STRUID_NUCLOSSTATE = T18.STRUID
         LEFT OUTER JOIN dibac.T_MD_STATE T22 ON T21.STRUID_NUCLOSSTATE = T22.STRUID
    WHERE 
        -- 若需全量查询，可注释掉t1.nummer的固定条件
        (t1.nummer = '20091') AND 
        (t2.intnumeral = '40') AND 
        (t3.summematerialwertl > 0 ) AND 
        (t18.intnumeral = '50') AND 
        (t19.emballagennummer NOT IN ('000', 'RST', 'MUS', 'ZZZ', '109')) AND 
        (t22.intnumeral = '40')
)
-- 仅保留每个组合的最新记录
SELECT *
FROM RankedCalculations
WHERE RowRank = 1
ORDER BY 
    Rezeptnummer ASC,
    Emballagennummer ASC,
    Kalkulationsposition ASC

关键说明 #

• PARTITION BY字段：确保按你需要的唯一组合分组，若组合逻辑有调整（比如不需要配方编号），可修改此处的字段列表
• 性能优化：全量查询时，建议给T1.NUMMER、T17.NUMMER、T19.EMBALLAGENNUMMER、T14.POSNR、T13.KALKULATIONSDATUM这些字段建立复合索引，提升窗口函数的执行效率
• 替代方案（WITH TIES）：若仅需按日期取最新批次，也可使用SELECT TOP 1 WITH TIES，但需确保排序逻辑和分组匹配：

  SELECT TOP 1 WITH TIES
      -- 原查询字段列表
  FROM 
      -- 原查询表连接逻辑
  WHERE 
      -- 原查询条件
  ORDER BY 
      ROW_NUMBER() OVER (
          PARTITION BY T1."NUMMER", T17."NUMMER", T19."EMBALLAGENNUMMER", T14."POSNR" 
          ORDER BY T13."KALKULATIONSDATUM" DESC
      )
  

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