按INV_CART_ID分组获取最大DEMAND_DATE的SQL查询问题

你的需求与现状 #

你需要针对每个INV_CART_ID，筛选出对应的最大DEMAND_DATE记录，同时保留BUSINESS_UNIT、INV_ITEM_ID、CART_COUNT_QTY等字段。

• 现有查询：

SELECT BUSINESS_UNIT, INV_CART_ID, INV_ITEM_ID, CART_COUNT_QTY, DEMAND_DATE 
FROM PS_CART_CT_INF_INV A 
WHERE A.INV_ITEM_ID = 1 
  AND A.BUSINESS_UNIT = '11MMS' 
  AND A.CART_COUNT_QTY <> 0 
ORDER BY DEMAND_DATE DESC

• 当前输出（存在重复的INV_CART_ID记录，未按购物车取最大日期）：

BUSINESS_UNIT INV_CART_ID     INV_ITEM_ID CART_COUNT_QTY DEMAND_DATE
11MMS         405             1           5.0000         2018-05-29
11MMS         405             1           5.0000         2018-05-28
11MMS         OUTPT_INFUSION  1           4.0000         2018-05-29
11MMS         OUTPT_INFUSION  1           4.0000         2018-05-27
11MMS         938             1           15.0000        2018-05-31
11MMS         938             1           15.0000        2018-05-30
...（其他重复记录）

• 期望输出（每个INV_CART_ID仅一条记录，对应该购物车的最大DEMAND_DATE）：

BUSINESS_UNIT INV_CART_ID     INV_ITEM_ID CART_COUNT_QTY DEMAND_DATE
11MMS         405             1           5.0000         2018-05-29
11MMS         OUTPT_INFUSION  1           4.0000         2018-05-29
11MMS         938             1           15.0000        2018-05-31
11MMS         286             1           1.0000         2018-05-07
11MMS         708             1           4.0000         2018-04-05

你尝试的查询逻辑存在偏差，导致无法返回所有INV_CART_ID且日期不正确，下面是修正后的方案：

问题分析 #

你的原查询存在两个核心问题：

• 子查询仅关联了INV_ITEM_ID，未关联INV_CART_ID，无法准确匹配每个购物车的最大日期
• 外层GROUP BY包含了CART_COUNT_QTY，若同一INV_CART_ID下有不同的CART_COUNT_QTY值，会被拆分为多条记录，同时可能错误聚合日期

正确解法1：使用窗口函数（推荐） #

窗口函数ROW_NUMBER()可以为每个INV_CART_ID分组，按DEMAND_DATE降序排序后取第一条（即最大日期的记录），逻辑清晰且性能优秀：

SELECT 
    BUSINESS_UNIT, 
    INV_CART_ID, 
    INV_ITEM_ID, 
    CART_COUNT_QTY, 
    DEMAND_DATE
FROM (
    SELECT 
        BUSINESS_UNIT, 
        INV_CART_ID, 
        INV_ITEM_ID, 
        CART_COUNT_QTY, 
        DEMAND_DATE,
        -- 按INV_CART_ID分组，日期降序排序，标记每条记录的序号
        ROW_NUMBER() OVER (PARTITION BY INV_CART_ID ORDER BY DEMAND_DATE DESC) AS rn
    FROM PS_CART_CT_INF_INV
    WHERE 
        INV_ITEM_ID = 1 
        AND BUSINESS_UNIT = '11MMS' 
        AND CART_COUNT_QTY <> 0
) t
-- 只保留每个分组的第一条记录（最大日期）
WHERE rn = 1
ORDER BY DEMAND_DATE DESC;

正确解法2：子查询关联原表 #

先通过子查询获取每个INV_CART_ID对应的最大DEMAND_DATE，再关联原表获取完整记录，适合不支持窗口函数的旧版数据库：

SELECT 
    A.BUSINESS_UNIT, 
    A.INV_CART_ID, 
    A.INV_ITEM_ID, 
    A.CART_COUNT_QTY, 
    A.DEMAND_DATE
FROM PS_CART_CT_INF_INV A
INNER JOIN (
    -- 先获取每个INV_CART_ID的最大日期
    SELECT 
        INV_CART_ID, 
        MAX(DEMAND_DATE) AS MAX_DEMAND_DATE
    FROM PS_CART_CT_INF_INV
    WHERE 
        INV_ITEM_ID = 1 
        AND BUSINESS_UNIT = '11MMS' 
        AND CART_COUNT_QTY <> 0
    GROUP BY INV_CART_ID
) B ON A.INV_CART_ID = B.INV_CART_ID AND A.DEMAND_DATE = B.MAX_DEMAND_DATE
WHERE 
    A.INV_ITEM_ID = 1 
    AND A.BUSINESS_UNIT = '11MMS' 
    AND A.CART_COUNT_QTY <> 0
ORDER BY A.DEMAND_DATE DESC;

这两种方法都能准确返回每个INV_CART_ID对应最大DEMAND_DATE的完整记录，完全匹配你的期望输出。

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