SQL聚合查询中SUM(field)结果重复问题咨询（需保留描述字段）

我需要查询零件的销售总量，但由于零件名称（T2.FrgnName）存在变体（同一VendorNum对应多个FrgnName），导致查询结果出现重复行，SUM(T0.Quantity)计算值重复。补充说明：VendorNum对应型号编码，同一型号存在N种变体，我要统计型号的销售总量，且需要保留零件描述字段。

当前查询语句及结果 #

SELECT T0.VendorNum
     ,T2.FrgnName 
     ,T4.Descr
     , T3.CardName
     , YEAR(T1.DocDate)[DATA]
     ,SUM(T0.Quantity)[QTDADE]
FROM RDR1 T0 -- (T0)SALES BODY
    JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry --(T1)SALES HEADER
    JOIN (SELECT DISTINCT SuppCatNum, CardCode, FrgnName FROM OITM) T2 ON T2.SuppCatNum = T0.VendorNum -- (T2)ITEMS
    JOIN (SELECT DISTINCT CardCode, CardName  FROM OCRD) T3 ON T3.CardCode = T2.CardCode -- (T3)TABLE SUPPLIER
    LEFT JOIN (SELECT DISTINCT IndexID, TableID, FieldID, Descr FROM UFD1) T4 ON T0.U_Tipo = T4.IndexID AND TableID = 'RDR1' AND FieldID = 12   --(T4)USER DEFINED TABLE
WHERE T0.DocDate >= '20200101' AND T0.DocDate <= '20201231'
    AND T3.CardCode = 'F00045'
    AND T0.VendorNum = 'CU03E'
    AND T1.CANCELED = 'N'
    AND ((T0.TargetType > 0 AND T0.LineStatus = 'C') OR (T0.LineStatus = 'O'))
GROUP BY T0.VendorNum, T2.FrgnName,T3.CardName, T4.Descr, YEAR(T1.DocDate)
ORDER BY T0.VendorNum

查询结果：

隐藏FrgnName后的查询及结果 #

隐藏T2.FrgnName字段后，统计总量恢复正确，但丢失了零件名称信息：

SELECT T0.VendorNum
    /* ,T2.FrgnName */
     ,T4.Descr
     , T3.CardName
     , YEAR(T1.DocDate)[DATA]
     ,SUM(T0.Quantity)[QTDADE]
FROM RDR1 T0
    JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry
    JOIN (SELECT DISTINCT SuppCatNum, CardCode, FrgnName FROM OITM) T2 ON T2.SuppCatNum = T0.VendorNum
    JOIN (SELECT DISTINCT CardCode, CardName  FROM OCRD) T3 ON T3.CardCode = T2.CardCode
    LEFT JOIN (SELECT DISTINCT IndexID, TableID, FieldID, Descr FROM UFD1) T4 ON T0.U_Tipo = T4.IndexID AND TableID = 'RDR1' AND FieldID = 12   
WHERE T0.DocDate >= '20200101' AND T0.DocDate <= '20201231'
    AND T3.CardCode = 'F00045'
    AND T0.VendorNum = 'CU03E'
    AND T1.CANCELED = 'N'
    AND ((T0.TargetType > 0 AND T0.LineStatus = 'C') OR (T0.LineStatus = 'O'))
GROUP BY T0.VendorNum, /*T2.FrgnName,*/ T3.CardName, T4.Descr, YEAR(T1.DocDate)
ORDER BY T0.VendorNum

查询结果：

解决方案 #

方法1：先统计总量，再关联聚合名称 #

先按型号维度统计正确的销售总量，再关联获取所有名称变体并聚合展示，结果行按型号唯一。

WITH SalesSummary AS (
    SELECT 
        T0.VendorNum,
        T4.Descr,
        T3.CardName,
        YEAR(T1.DocDate) AS [DATA],
        SUM(T0.Quantity) AS [QTDADE]
    FROM RDR1 T0
    JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry
    JOIN (SELECT DISTINCT SuppCatNum, CardCode FROM OITM) T2 ON T2.SuppCatNum = T0.VendorNum
    JOIN (SELECT DISTINCT CardCode, CardName FROM OCRD) T3 ON T3.CardCode = T2.CardCode
    LEFT JOIN (SELECT DISTINCT IndexID, TableID, FieldID, Descr FROM UFD1) T4 ON T0.U_Tipo = T4.IndexID AND TableID = 'RDR1' AND FieldID = 12
    WHERE T0.DocDate >= '20200101' AND T0.DocDate <= '20201231'
        AND T3.CardCode = 'F00045'
        AND T0.VendorNum = 'CU03E'
        AND T1.CANCELED = 'N'
        AND ((T0.TargetType > 0 AND T0.LineStatus = 'C') OR (T0.LineStatus = 'O'))
    GROUP BY T0.VendorNum, T3.CardName, T4.Descr, YEAR(T1.DocDate)
)
SELECT 
    ss.VendorNum,
    STRING_AGG(DISTINCT T2.FrgnName, ', ') AS FrgnName, -- SQL Server 用STRING_AGG；MySQL 替换为GROUP_CONCAT(DISTINCT T2.FrgnName SEPARATOR ', ')
    ss.Descr,
    ss.CardName,
    ss.[DATA],
    ss.[QTDADE]
FROM SalesSummary ss
JOIN (SELECT DISTINCT SuppCatNum, FrgnName FROM OITM) T2 ON T2.SuppCatNum = ss.VendorNum
GROUP BY ss.VendorNum, ss.Descr, ss.CardName, ss.[DATA], ss.[QTDADE]
ORDER BY ss.VendorNum;

方法2：用窗口函数计算总量，保留所有名称变体 #

使用窗口函数按型号维度计算总量，保留每个名称变体行，但总量值正确。

SELECT DISTINCT
    T0.VendorNum,
    T2.FrgnName,
    T4.Descr,
    T3.CardName,
    YEAR(T1.DocDate) AS [DATA],
    SUM(T0.Quantity) OVER(PARTITION BY T0.VendorNum, T4.Descr, T3.CardName, YEAR(T1.DocDate)) AS [QTDADE]
FROM RDR1 T0
JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry
JOIN (SELECT DISTINCT SuppCatNum, CardCode, FrgnName FROM OITM) T2 ON T2.SuppCatNum = T0.VendorNum
JOIN (SELECT DISTINCT CardCode, CardName FROM OCRD) T3 ON T3.CardCode = T2.CardCode
LEFT JOIN (SELECT DISTINCT IndexID, TableID, FieldID, Descr FROM UFD1) T4 ON T0.U_Tipo = T4.IndexID AND TableID = 'RDR1' AND FieldID = 12
WHERE T0.DocDate >= '20200101' AND T0.DocDate <= '20201231'
    AND T3.CardCode = 'F00045'
    AND T0.VendorNum = 'CU03E'
    AND T1.CANCELED = 'N'
    AND ((T0.TargetType > 0 AND T0.LineStatus = 'C') OR (T0.LineStatus = 'O'))
ORDER BY T0.VendorNum;

方法说明 #

• 方法1适合需要将同一型号的所有名称变体合并展示，且结果行唯一的场景。
• 方法2适合需要保留每个名称变体行，但确保总量统计正确的场景。

内容的提问来源于stack exchange，提问作者Farley A Souza