SQL：根據CASE回傳不同的SUM

我有兩個有Header/Detail關系的表，我正在創建一個視圖。
視圖 ( OrderQty) 中某一列的計算取決于已聽到的表 ( Status) 中某一列的值。
因此，根據Status我希望OrderQty成為不同欄位的總和，這就是我嘗試過的：

       (CASE
             WHEN so.Status = 10 THEN (SELECT SUM(sod.OrderQty - sod.BackOrderQty - sod.CancelQty) AS v
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
             WHEN so.Status = 20 THEN (SELECT SUM(sod.DespatchQty)
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
             WHEN so.Status = 30
               OR so.Status = 40 THEN (SELECT SUM(ISNULL(sod.PODQty, sod.DespatchQty))
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
             WHEN so.Status = 50
               OR so.Status = 60 THEN (SELECT SUM(sod.InvoiceQty)
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
        END) AS OrderQty;

整體觀點是：

SELECT so.SalesOrderID,
       so.SalesOrderJobTypeID,
       so.CarrierCodeID,
       so.SalesOrderTransportStatusID,
       so.CollectionDateTime,
       so.CustomerReference,
       so.FileReference,
       so.RequiredDate,
       so.SpecialInstructions,
       so.DeliveryNotes,
       dbo.ufn_SO_DecodeSOHeaderStatus(so.Status) AS Status,
       so.TransportCost,
       so.TransportRevenue,
       so.CurrencyCode,
       customer.AccountCode,
       customer.CustomerName,
       customer.Address1,
       customer.Address2,
       customer.Address3,
       customer.Address4,
       customer.PostCode,
       (SELECT UserName
        FROM dbo.SecurityUser
        WHERE dbo.SecurityUser.UserID = so.CreatedByUserID) AS CreatedByUser,
       (SELECT WeekNumber
        FROM dbo.Calendars
        WHERE dbo.Calendars.Date = so.RequiredDate) AS WeekNumber,
       (MONTH(so.RequiredDate)) AS MonthNumber,
       (SELECT ShortCode
        FROM dbo.StoreCodes
        WHERE dbo.StoreCodes.StoreCodeID = so.StoreCodeID) AS StoreCode,
       ISNULL((SELECT PickedQty
               FROM dbo.SO_SOAllocatedContainersView
               WHERE dbo.SO_SOAllocatedContainersView.SalesOrderID = so.SalesOrderID),
              0) AS PickedQty,
       (CASE
             WHEN so.Status = 10 THEN (SELECT SUM(sod.OrderQty - sod.BackOrderQty - sod.CancelQty) AS v
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
             WHEN so.Status = 20 THEN (SELECT SUM(sod.DespatchQty)
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
             WHEN so.Status = 30
               OR so.Status = 40 THEN (SELECT SUM(ISNULL(sod.PODQty, sod.DespatchQty))
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
             WHEN so.Status = 50
               OR so.Status = 60 THEN (SELECT SUM(sod.InvoiceQty)
                                       FROM dbo.SalesOrderDetail sod
                                       WHERE sod.SalesOrderID = so.SalesOrderID
                                         AND sod.Status <> 99)
        END) AS OrderQty
FROM dbo.SalesOrderHeader so
     JOIN dbo.Customer customer ON so.CustomerID = customer.CustomerID;

這給出了錯誤：Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
有誰了解我想要做什么并可以提供解決方案？

uj5u.com熱心網友回復：

顯然這是無法測驗的，但是您應該能夠采用以下方法：

使用通用條件加入您的 SalesOrderDetail 表，并對所需列求和，然后在更簡單的 case 運算式中使用結果。

使用 appy()

...
from dbo.SalesOrderHeader so
join dbo.Customer customer on so.CustomerID = customer.CustomerID
outer apply (
    select Sum(OrderQty) OrderQty, Sum(BackOrderQty) BackOrderQty ... etc
    from dbo.SalesOrderDetail sod
    where sod.SalesOrderID = so.SalesOrderID
    and sod.Status <> 99
)sod;

然后你的情況變成

case 
  when so.status = 10 then sod.OrderQty - sod.BackOrderQty - sod.CancelQty 
  when so.status = 20 then sod.DespatchQty
  ... etc
end

標籤：

上一篇：T-SQLUPDATE查詢以填充NULL列

下一篇：在SQL中計劃重繪