按物料汇总唯一总量的SQL查询优化需求

需求说明 #

需要实现按物料维度汇总唯一总量：

• 同一物料的重复总量值仅保留一次
• 同一物料的不同总量值需求和合并

原SQL查询 #

WITH MaterialVolume
AS
(
SELECT          m.LookupCode as materials,
                sum( distinct lpc.PackagedAmount) as totalQty,
                (mpl.Length * mpl.Width * mpl.Height * sum(distinct lpc.PackagedAmount)) / 1728  as volumeQty,
                lpc.captureDate

FROM            dbo.Materials m 
JOIN            dbo.MaterialsPackagingsLookup mpl on m.id = mpl.MaterialId
JOIN            dbo.Lots l on m.id = l.MaterialId
JOIN            dbo.LicensePlateContentsSnapshot lpc on lpc.LotId = l.id
JOIN            dbo.Projects p on m.ProjectId = p.id

WHERE         p.LookupCode = 'Neighbor'
  AND m.LookupCode in('45934MR-F01600','45934HR-F01461','45935HR-F01461')


GROUP BY        m.LookupCode,
                mpl.Length,
                mpl.Width,
                mpl.Height,
                lpc.captureDate

)

SELECT          materials,
                totalQty,
                round(volumeQty,0) as roundTotalVolume,
                captureDate

FROM            MaterialVolume

ORDER BY        materials,captureDate

当前查询结果问题 #

原查询按物料、包装尺寸和日期分组，导致同一物料因日期不同重复出现，部分物料存在多组不同总量值：

需要实现的逻辑：

• 物料45934HR-F01461的roundTotalVolume为126，重复多次，仅保留该值
• 物料45934MR-F01600有68和136两种值，汇总为68+136=204
• 物料45935HR-F01461有126和525两种值，汇总为126+525=651

优化后的SQL查询 #

WITH MaterialVolume AS (
    SELECT 
        m.LookupCode AS materials,
        SUM(DISTINCT lpc.PackagedAmount) AS totalQty,
        (mpl.Length * mpl.Width * mpl.Height * SUM(DISTINCT lpc.PackagedAmount)) / 1728 AS volumeQty,
        lpc.captureDate
    FROM dbo.Materials m 
    JOIN dbo.MaterialsPackagingsLookup mpl ON m.id = mpl.MaterialId
    JOIN dbo.Lots l ON m.id = l.MaterialId
    JOIN dbo.LicensePlateContentsSnapshot lpc ON lpc.LotId = l.id
    JOIN dbo.Projects p ON m.ProjectId = p.id
    WHERE 
        p.LookupCode = 'Neighbor'
        AND m.LookupCode IN('45934MR-F01600','45934HR-F01461','45935HR-F01461')
    GROUP BY 
        m.LookupCode,
        mpl.Length,
        mpl.Width,
        mpl.Height,
        lpc.captureDate
),
UniqueMaterialValues AS (
    -- 先按物料和去重后的roundTotalVolume分组，确保同一物料的相同值只保留一次
    SELECT 
        materials,
        MAX(totalQty) AS totalQty, -- 同一值对应的totalQty一致，取MAX/MIN均可
        ROUND(volumeQty, 0) AS roundTotalVolume
    FROM MaterialVolume
    GROUP BY materials, ROUND(volumeQty, 0)
)
-- 最终按物料汇总求和
SELECT 
    materials,
    SUM(totalQty) AS totalQty,
    SUM(roundTotalVolume) AS roundTotalVolume
FROM UniqueMaterialValues
GROUP BY materials
ORDER BY materials;

优化后查询结果 #

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