如何识别Oracle中客户参考、商品及数量一致的重复订单

核心逻辑 #

把每个订单的客户参考+所有商品及对应数量打包成唯一标识，通过这个标识筛选出订单号不同的重复订单组，再关联原数据生成完整报告。

分步实现方案 #

• 第一步：聚合订单的商品与数量信息
  用Oracle 11gR2支持的LISTAGG函数，将每个订单下的商品编码、数量按固定格式拼接，同时按商品编码排序，避免因商品顺序不同导致误判。
  示例SQL片段：

  SELECT 
      order_id,
      customer_ref,
      LISTAGG(item_code || ':' || item_qty, ',') WITHIN GROUP (ORDER BY item_code) AS order_items_key
  FROM 
      order_details
  GROUP BY 
      order_id, customer_ref
  

  注：order_details为订单明细表，order_id是订单号，customer_ref是客户参考，item_code商品编码，item_qty商品数量。

• 第二步：识别重复订单组
  基于上面的聚合结果，按customer_ref和order_items_key分组，筛选出组内订单数大于1的记录，这些就是重复订单组。
  示例SQL：

  WITH order_item_agg AS (
      SELECT 
          order_id,
          customer_ref,
          LISTAGG(item_code || ':' || item_qty, ',') WITHIN GROUP (ORDER BY item_code) AS order_items_key
      FROM 
          order_details
      GROUP BY 
          order_id, customer_ref
  )
  SELECT 
      customer_ref,
      order_items_key,
      LISTAGG(order_id, ',') WITHIN GROUP (ORDER BY order_id) AS duplicate_order_ids
  FROM 
      order_item_agg
  GROUP BY 
      customer_ref, order_items_key
  HAVING 
      COUNT(order_id) > 1
  

  该查询会输出每个重复组的客户参考、商品数量组合，以及对应的所有重复订单号。

• 第三步：生成详细重复订单报告
  如果需要展示重复订单的完整明细，可关联订单主表和明细表输出：

  WITH order_item_agg AS (
      SELECT 
          order_id,
          customer_ref,
          LISTAGG(item_code || ':' || item_qty, ',') WITHIN GROUP (ORDER BY item_code) AS order_items_key
      FROM 
          order_details
      GROUP BY 
          order_id, customer_ref
  ),
  duplicate_groups AS (
      SELECT 
          customer_ref,
          order_items_key
      FROM 
          order_item_agg
      GROUP BY 
          customer_ref, order_items_key
      HAVING 
          COUNT(order_id) > 1
  )
  SELECT 
      o.order_id,
      o.customer_ref,
      od.item_code,
      od.item_qty
  FROM 
      orders o
  JOIN order_details od ON o.order_id = od.order_id
  JOIN order_item_agg oia ON o.order_id = oia.order_id
  JOIN duplicate_groups dg ON oia.customer_ref = dg.customer_ref AND oia.order_items_key = dg.order_items_key
  ORDER BY 
      o.customer_ref, oia.order_items_key, o.order_id, od.item_code
  

  注：orders为订单主表，包含订单基础信息。

特殊情况处理 #

• 拼接字符串长度限制：如果订单商品过多，LISTAGG拼接结果可能超过VARCHAR2默认4000字节限制，可改用XMLAGG或结合MD5哈希压缩：

  SELECT 
      order_id,
      customer_ref,
      DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(LISTAGG(item_code || ':' || item_qty, ',') WITHIN GROUP (ORDER BY item_code), 'AL32UTF8'), DBMS_CRYPTO.HASH_MD5) AS order_items_md5
  FROM 
      order_details
  GROUP BY 
      order_id, customer_ref
  

  需提前给用户授予DBMS_CRYPTO权限。
• 空值处理：若商品编码或数量存在空值，用NVL函数替换为特定标识（比如''或'NULL'），避免聚合结果不一致。

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