基于ItemName和Color匹配的POQty与DCQty分摊SQL查询问题排查及优化

我来帮你排查现有方案的问题，并给出正确的实现思路和代码。

一、现有方案的错误分析 #

1. CTE方案的问题 #

你的CTE逻辑只尝试处理前两条记录，而且没有正确计算累积已分摊的PO总量，导致第二条记录的DCQty计算错误。比如在示例中，你直接用初始DCQty减去第一条的分摊量，但没有判断第一条的POQty是否已经超过DCQty，后续行也没有基于剩余DCQty来计算，最终出现了负的POBalQty，逻辑完全走偏了。

2. 临时表方案的问题 #

• 问题一：循环条件里的SUM(POQty) <= DCQty判断有问题。当DCQty小于单个POQty时，第一次循环（@i=1）的SUM(POQty)就是第一条的POQty，此时这个SUM值大于DCQty，循环直接不执行，所以没有结果返回。
• 问题二：CASE语句的分支不完整。当DCQty等于前i-1条PO的总和时，POQty既不大于也不小于DCQty - SUM(前i-1)，CASE没有匹配的分支，返回NULL，导致K2的DCQty为空。

二、正确的SQL实现方案 #

我们可以利用窗口函数SUM() OVER()来计算每个PO的累积总量，这样就能轻松判断当前PO能分摊多少DCQty，无需复杂的CTE嵌套或循环。

实现代码 #

SELECT
    p.PONO,
    p.ItemName,
    p.Color,
    p.POQty,
    -- 计算当前PO可分摊的DCQty
    CASE
        -- 如果没有匹配的DC记录，分摊0
        WHEN d.DCQty IS NULL THEN 0
        -- 前面的PO总和已经耗尽DCQty，当前PO分摊0
        WHEN SUM(p.POQty) OVER (PARTITION BY p.ItemName, p.Color ORDER BY p.PONO ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) >= d.DCQty THEN 0
        -- 剩余DCQty足够覆盖当前PO，分摊全部POQty
        WHEN (SUM(p.POQty) OVER (PARTITION BY p.ItemName, p.Color ORDER BY p.PONO)) <= d.DCQty THEN p.POQty
        -- 剩余DCQty不足覆盖当前PO，分摊剩余的DCQty
        ELSE d.DCQty - SUM(p.POQty) OVER (PARTITION BY p.ItemName, p.Color ORDER BY p.PONO ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    END AS DCQtyAllocated,
    -- 计算PO剩余量
    p.POQty - CASE
        WHEN d.DCQty IS NULL THEN 0
        WHEN SUM(p.POQty) OVER (PARTITION BY p.ItemName, p.Color ORDER BY p.PONO ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) >= d.DCQty THEN 0
        WHEN (SUM(p.POQty) OVER (PARTITION BY p.ItemName, p.Color ORDER BY p.PONO)) <= d.DCQty THEN p.POQty
        ELSE d.DCQty - SUM(p.POQty) OVER (PARTITION BY p.ItemName, p.Color ORDER BY p.PONO ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    END AS POBalQty
FROM [dbo].[PO] p
LEFT JOIN [dbo].[DC] d 
    ON p.ItemName = d.ItemName AND p.Color = d.Color
-- 如果需要筛选特定Item和Color，加上WHERE条件
-- WHERE p.ItemName = 'A1' AND p.Color = 'C1'
ORDER BY p.ItemName, p.Color, p.PONO;

代码说明 #

1. 窗口函数计算累积量：SUM(p.POQty) OVER (PARTITION BY p.ItemName, p.Color ORDER BY p.PONO) 计算到当前PO为止的累积PO总量；ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING 则计算当前PO之前所有PO的总量。
2. 分支判断逻辑：
   • 优先处理没有匹配DC记录的情况，直接返回0；
   • 如果前面的PO总和已经超过DCQty，当前PO不分摊；
   • 如果累积到当前PO的总量还小于等于DCQty，当前PO全部分摊；
   • 否则，分摊剩余的DCQty。

测试结果验证 #

示例DCQty=9500的情况：

PONO ItemName Color POQty DCQtyAllocated POBalQty
-------------------------------------------------
K1   A1       C1    5240  5240           0
K2   A1       C1    393   393            0
K3   A1       C1    4847  3867           980
K4   A3       C3    2968  0              2968
K5   A4       C4    3224  0              3224

完全符合规则：K1和K2全部分摊，剩余DCQty=9500-5240-393=3867分摊给K3，K3剩余980，其他无匹配的PO分摊0。

DCQty=5000的情况（小于K1的POQty）：

PONO ItemName Color POQty DCQtyAllocated POBalQty
-------------------------------------------------
K1   A1       C1    5240  5000           240
K2   A1       C1    393   0              393
K3   A1       C1    4847  0              4847
...（其他行同上）

K1分摊5000，剩余240，后续PO不再分摊，符合要求。

DCQty=5633（等于K1+K2的总和）：

PONO ItemName Color POQty DCQtyAllocated POBalQty
-------------------------------------------------
K1   A1       C1    5240  5240           0
K2   A1       C1    393   393            0
K3   A1       C1    4847  0              4847
...（其他行同上）

K1和K2全部分摊，DCQty耗尽，后续PO不分摊，解决了临时表方案的NULL问题。

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