如何在SQL查询结果集中抑制TotalCarton列的重复值？

我明白你想让TotalCarton列只在首次出现时显示数值，后续重复行显示空值或NULL的需求，咱们来一步步搞定这个问题。

首先看你当前的查询，核心问题在于你直接在主查询中按PackType计算TotalCarton，同时又将BAX_PACK_DTL.OuterPackID加入了GROUP BY子句——这会导致每个OuterPackID对应一行结果，同一个订单的TotalCarton值就会重复出现在每一行里。

要实现重复值抑制，我们可以用窗口函数LAG()来对比当前行与上一行的TotalCarton值，或者先预计算订单级别的聚合结果再关联主数据。这里推荐窗口函数方案，更简洁直观：

修改后的SQL脚本 #

WITH OrderAgg AS (
    SELECT 
        ORDERS.StorerKey,
        ORDERS.OrderKey,
        PackKey = (SELECT MAX(PackKey) FROM BAX_PACK_DTL WITH (NOLOCK) WHERE OrderKey = ORDERS.OrderKey),
        SalesOrderNum = (SELECT Upper(Max(ORDERDETAIL.CustShipInst01)) FROM ORDERDETAIL WITH (NOLOCK) WHERE OrderKey = ORDERS.OrderKey),
        DeliveryNum = Upper(ORDERS.ExternOrderKey),
        -- 计算订单级别的TotalCarton和TotalPallet
        TotalCarton = SUM(CASE WHEN BAX_PACK_DTL.PackType = 'C' THEN 1 ELSE 0 END),
        TotalPallet = SUM(CASE WHEN BAX_PACK_DTL.PackType = 'P' THEN 1 ELSE 0 END),
        SumCarton = (SELECT COUNT(DISTINCT(OuterPackSeq)) FROM BAX_PACK_DTL WITH (NOLOCK) WHERE PackType = 'C' AND PackKey = '0000000211'),
        SumPallet = (SELECT COUNT(DISTINCT(OuterPackSeq)) FROM BAX_PACK_DTL WITH (NOLOCK) WHERE PackType = 'P' AND PackKey = '0000000211'),
        AddWho = Upper(ORDERS.EditWho),
        ORDERS.AddDate,
        BAX_PACK_DTL.OuterPackID,
        BAX_PACK_DTL.PackType
    FROM ORDERS WITH (NOLOCK)
    INNER JOIN ORDERDETAIL WITH (NOLOCK) 
        ON ORDERS.StorerKey = ORDERDETAIL.StorerKey 
        AND ORDERS.OrderKey = ORDERDETAIL.OrderKey
    INNER JOIN PICKDETAIL WITH (NOLOCK) 
        ON ORDERDETAIL.StorerKey = PICKDETAIL.StorerKey 
        AND ORDERDETAIL.OrderKey = PICKDETAIL.OrderKey 
        AND ORDERDETAIL.OrderLineNumber = PICKDETAIL.OrderLineNumber
    INNER JOIN BAX_PACK_DTL WITH (NOLOCK) 
        ON PICKDETAIL.OrderKey = BAX_PACK_DTL.OrderKey 
        AND PICKDETAIL.PickDetailKey = BAX_PACK_DTL.PickDetailKey
    WHERE (SELECT COUNT(DISTINCT(ORDERKEY)) FROM PICKDETAIL WITH (NOLOCK) WHERE OrderKey = ORDERS.OrderKey ) > 0
      AND BAX_PACK_DTL.PackKey = '0000000211'
      AND BAX_PACK_DTL.OuterPackID IN ('P111111111', 'P22222222', 'P33333333')
    GROUP BY ORDERS.StorerKey, ORDERS.OrderKey, ORDERS.ExternOrderKey, ORDERS.EditWho, ORDERS.AddDate, BAX_PACK_DTL.OuterPackID, BAX_PACK_DTL.PackType
)
SELECT 
    StorerKey,
    OrderKey,
    PackKey,
    OuterPackID AS PackHU,
    SalesOrderNum,
    DeliveryNum,
    -- 用LAG函数判断是否与上一行TotalCarton重复，重复则显示NULL
    CASE 
        WHEN LAG(TotalCarton) OVER (PARTITION BY OrderKey ORDER BY OuterPackID) = TotalCarton THEN NULL
        ELSE TotalCarton
    END AS TotalCarton,
    TotalPallet,
    SumCarton,
    SumPallet,
    AddWho,
    AddDate
FROM OrderAgg
ORDER BY OuterPackID ASC;

方案说明 #

1. CTE预聚合：先用通用表表达式（CTE）OrderAgg计算基础数据，包括订单级别的TotalCarton和TotalPallet（这里用SUM(CASE...)替代原查询的COUNT(DISTINCT)，因为已经按OuterPackID分组，每个分组对应一个唯一的OuterPackID，SUM更高效且结果一致）。
2. 窗口函数去重：在最终查询中使用LAG()窗口函数，按OrderKey分区（同一订单内对比）、OuterPackID排序，若当前行TotalCarton与上一行相同则显示NULL，否则显示原值，以此实现重复值抑制。

如果你希望重复值显示为空字符串而非NULL，只需将THEN NULL改为THEN ''即可。

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