SQL查询优化需求：合并指定BranchCode的QuantityAvailable数值

需求说明 #

现有表包含字段BranchID、BranchCode、PartNumber、SupplierCode、QuantityAvailable，表内有上千种PartNumber，以GP6504-12-12为例。需要实现：

• 保留非目标分支的QuantityAvailable原值
• 将BranchCode为0900-HSI和0100-BLA的库存求和
  支持两种输出形式：

1. 两个目标分支都显示求和后的总库存
2. 只保留其中一个目标分支，显示求和后的总库存

原查询错误点 #

原SQL的CASE语句里，错误地把目标分支的库存值替换成了固定值1，而非取实际的QuantityAvailable来计算总和；同时按BranchID、BranchCode分组，导致两个目标分支的库存无法合并计算，最终结果完全不符合预期。

形式一：两个目标分支均显示总和 #

通过CTE先计算出两个目标分支的总库存，再将两个目标分支的记录与总库存关联，最后和其他分支的记录合并：

WITH TargetBranchTotal AS (
    SELECT
        PartNumber,
        SupplierCode,
        SUM(QuantityAvailable) AS TotalQty
    FROM [mydatabase].[dbo].[mytable]
    WHERE
        PartType IN ('Part', 'Exchange')
        AND Inactive = 0
        AND PartNumber = 'GP6504-12-12'
        AND BranchCode IN ('0100-BLA', '0900-HSI')
    GROUP BY PartNumber, SupplierCode
)
SELECT
    t.BranchID,
    t.BranchCode,
    t.PartNumber,
    t.SupplierCode,
    tb.TotalQty AS QuantityAvailable
FROM [mydatabase].[dbo].[mytable] t
JOIN TargetBranchTotal tb 
    ON t.PartNumber = tb.PartNumber 
    AND t.SupplierCode = tb.SupplierCode
WHERE
    PartType IN ('Part', 'Exchange')
    AND Inactive = 0
    AND PartNumber = 'GP6504-12-12'
    AND BranchCode IN ('0100-BLA', '0900-HSI')
UNION ALL
SELECT
    BranchID,
    BranchCode,
    PartNumber,
    SupplierCode,
    QuantityAvailable
FROM [mydatabase].[dbo].[mytable]
WHERE
    PartType IN ('Part', 'Exchange')
    AND Inactive = 0
    AND PartNumber = 'GP6504-12-12'
    AND BranchCode NOT IN ('0100-BLA', '0900-HSI')

形式二：仅保留其中一个目标分支的总和 #

以保留0100-BLA分支为例，计算两个目标分支的总库存后，只输出0100-BLA的记录并携带总库存，其他分支正常输出：

WITH TargetBranchTotal AS (
    SELECT
        PartNumber,
        SupplierCode,
        SUM(QuantityAvailable) AS TotalQty,
        MAX(CASE WHEN BranchCode = '0100-BLA' THEN BranchID END) AS KeepBranchID,
        '0100-BLA' AS KeepBranchCode
    FROM [mydatabase].[dbo].[mytable]
    WHERE
        PartType IN ('Part', 'Exchange')
        AND Inactive = 0
        AND PartNumber = 'GP6504-12-12'
        AND BranchCode IN ('0100-BLA', '0900-HSI')
    GROUP BY PartNumber, SupplierCode
)
SELECT
    tb.KeepBranchID AS BranchID,
    tb.KeepBranchCode AS BranchCode,
    tb.PartNumber,
    tb.SupplierCode,
    tb.TotalQty AS QuantityAvailable
FROM TargetBranchTotal tb
UNION ALL
SELECT
    BranchID,
    BranchCode,
    PartNumber,
    SupplierCode,
    QuantityAvailable
FROM [mydatabase].[dbo].[mytable]
WHERE
    PartType IN ('Part', 'Exchange')
    AND Inactive = 0
    AND PartNumber = 'GP6504-12-12'
    AND BranchCode NOT IN ('0100-BLA', '0900-HSI')

如果要保留0900-HSI，只需修改CTE里的CASE条件和KeepBranchCode的值即可。

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