如何计算各库位拣货量占总拣货量的百分比？

早上好呀！刚好我之前也处理过类似的SQL百分比计算需求，给你两种可行的方案来实现每个库位拣货量占总拣货量的百分比：

方法1：子查询获取总拣货量 #

这种方法通过子查询先算出符合条件的总拣货数，再和每个库位的拣货数做除法计算占比：

DECLARE @pYWK INT 
SET @pYWK = (SELECT YWK FROM CHDS_Management.dbo.Calendar WHERE DT = (SELECT DATEADD(WEEK,-3, CONVERT(DATE,GETDATE())))); 

SELECT 
    ORD.ProductWhsLocation AS 'Location', 
    COUNT(ORD.ProductWhsLocation) AS 'Picked',
    -- 计算百分比并保留两位小数，注意转FLOAT避免整数除法
    ROUND(CAST(COUNT(ORD.ProductWhsLocation) AS FLOAT) * 100.0 / (
        SELECT COUNT(*) 
        FROM CHDS_Common.dbo.OMOrder AS ORD_SUB
        INNER JOIN CHDS_Management.dbo.PickZoneControl AS PZC_SUB ON LEFT(ORD_SUB.ProductWhsLocation,3) = PZC_SUB.PickZone 
        INNER JOIN CHDS_Management.dbo.Calendar AS CAL_SUB ON CAL_SUB.DT = ORD_SUB.EarliestPickDate 
        WHERE CAL_SUB.YWK = @pYWK AND ORD_SUB.PickedQty <> 0
    ), 2) AS 'Percentage'
FROM CHDS_Common.dbo.OMOrder AS ORD 
INNER JOIN CHDS_Management.dbo.PickZoneControl AS PZC ON LEFT(ORD.ProductWhsLocation,3) = PZC.PickZone 
INNER JOIN CHDS_Management.dbo.Calendar AS CAL ON CAL.DT = ORD.EarliestPickDate 
WHERE CAL.YWK = @pYWK AND ORD.PickedQty <> 0 
GROUP BY ORD.ProductWhsLocation 

关键点说明： #

• 子查询完全复用了主查询的过滤条件，确保总拣货数和每个库位的拣货数是同一时间段（指定周）、已完成拣货的有效数据
• 必须把COUNT()结果转成FLOAT类型，否则SQL会执行整数除法（比如5/100会得到0而不是0.05）
• 用ROUND(...,2)把结果保留两位小数，符合百分比的展示需求

方法2：窗口函数（推荐，更高效） #

如果你的SQL版本支持窗口函数（比如SQL Server 2008及以上），这种方法更简洁高效，只需要扫描一次数据就能得到结果：

DECLARE @pYWK INT 
SET @pYWK = (SELECT YWK FROM CHDS_Management.dbo.Calendar WHERE DT = (SELECT DATEADD(WEEK,-3, CONVERT(DATE,GETDATE())))); 

SELECT 
    Location,
    Picked,
    ROUND(CAST(Picked AS FLOAT) * 100.0 / TotalPicked, 2) AS 'Percentage'
FROM (
    SELECT 
        ORD.ProductWhsLocation AS 'Location', 
        COUNT(ORD.ProductWhsLocation) AS 'Picked',
        -- 用窗口函数计算所有库位的总拣货量
        SUM(COUNT(ORD.ProductWhsLocation)) OVER() AS TotalPicked
    FROM CHDS_Common.dbo.OMOrder AS ORD 
    INNER JOIN CHDS_Management.dbo.PickZoneControl AS PZC ON LEFT(ORD.ProductWhsLocation,3) = PZC.PickZone 
    INNER JOIN CHDS_Management.dbo.Calendar AS CAL ON CAL.DT = ORD.EarliestPickDate 
    WHERE CAL.YWK = @pYWK AND ORD.PickedQty <> 0 
    GROUP BY ORD.ProductWhsLocation 
) AS SubQuery

关键点说明： #

• SUM(COUNT(ORD.ProductWhsLocation)) OVER() 会计算整个分组结果集的COUNT总和，也就是总拣货量，不需要重复写子查询
• 外层直接用每个库位的Picked除以TotalPicked计算占比，逻辑更清晰
• 同样要注意转成FLOAT避免整数除法的问题

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