SQL查询去重需求：Oracle运单关联查询结果出现重复值的解决

方法1：使用DISTINCT关键字去重 #

这是最直接的方式，在SELECT后添加DISTINCT即可过滤完全重复的行：

SELECT DISTINCT
  BUY.SHIPMENT_XID BUYSHIPMENT,
  SELL.SHIPMENT_XID SELLSHIPMENT
FROM 
  SHIPMENT SELL, 
  V30_SHIPMENT_ORDER_RELEASE V30, 
  V30_SHIPMENT_ORDER_RELEASE V300, 
  SHIPMENT BUY
WHERE 
  SELL.PERSPECTIVE = 'S'
  AND SELL.SHIPMENT_GID = V30.SHIPMENT_GID 
  AND V30.ORDER_RELEASE_GID = V300.ORDER_RELEASE_GID 
  AND V300.PERSPECTIVE = 'B'
  AND V300.SHIPMENT_GID = BUY.SHIPMENT_GID 

方法2：使用GROUP BY分组去重 #

通过对目标运单字段分组，也能得到唯一的运单配对结果：

SELECT
  BUY.SHIPMENT_XID BUYSHIPMENT,
  SELL.SHIPMENT_XID SELLSHIPMENT
FROM 
  SHIPMENT SELL, 
  V30_SHIPMENT_ORDER_RELEASE V30, 
  V30_SHIPMENT_ORDER_RELEASE V300, 
  SHIPMENT BUY
WHERE 
  SELL.PERSPECTIVE = 'S'
  AND SELL.SHIPMENT_GID = V30.SHIPMENT_GID 
  AND V30.ORDER_RELEASE_GID = V300.ORDER_RELEASE_GID 
  AND V300.PERSPECTIVE = 'B'
  AND V300.SHIPMENT_GID = BUY.SHIPMENT_GID 
GROUP BY BUY.SHIPMENT_XID, SELL.SHIPMENT_XID

方法3：使用窗口函数（适配需保留更多字段的场景） #

如果后续需要返回更多关联字段，不想丢失原始关联信息，可借助ROW_NUMBER()窗口函数给每个运单对编号，仅保留编号为1的行：

SELECT BUYSHIPMENT, SELLSHIPMENT
FROM (
  SELECT
    BUY.SHIPMENT_XID BUYSHIPMENT,
    SELL.SHIPMENT_XID SELLSHIPMENT,
    ROW_NUMBER() OVER (PARTITION BY BUY.SHIPMENT_XID, SELL.SHIPMENT_XID ORDER BY V30.ORDER_RELEASE_GID) AS rn
  FROM 
    SHIPMENT SELL, 
    V30_SHIPMENT_ORDER_RELEASE V30, 
    V30_SHIPMENT_ORDER_RELEASE V300, 
    SHIPMENT BUY
  WHERE 
    SELL.PERSPECTIVE = 'S'
    AND SELL.SHIPMENT_GID = V30.SHIPMENT_GID 
    AND V30.ORDER_RELEASE_GID = V300.ORDER_RELEASE_GID 
    AND V300.PERSPECTIVE = 'B'
    AND V300.SHIPMENT_GID = BUY.SHIPMENT_GID 
) t
WHERE rn = 1

补充说明 #

重复出现的核心原因是同一采购/销售运单配对关联了多个ORDER_RELEASE_GID，上述三种方法均可解决该问题：DISTINCT和GROUP BY适合仅需返回运单编号的简单场景，窗口函数则更灵活，可扩展支持返回更多关联字段的需求。

内容的提问来源于stack exchange，提问作者bjorn.delaat