問題描述

我有兩個表，一個 Orders 表，其中包含一個用戶訂單列表和一個 OrderShippingCosts 表，其中包含基于 Orders 表中的 >OrderTypeID.

I have two tables, an Orders table which contains a list of a users orders and a OrderShippingCosts table which contains a price for shipping each item based on the OrderTypeID in the Orders table.

我正在運行如下查詢來計算總運費:

I am running a query like below to calculate the total shipping costs:

SELECT
    SUM(CASE 
           WHEN OR.OrderTypeID = 1 
              THEN (SELECT CostOfShippingSmallParcel 
                    FROM OrderShippingCosts) 
              ELSE (SELECT CostOfShippingBigParcel 
                    FROM OrderShippingCosts) 
        END) AS TotalShippingCost
FROM 
    Orders AS OR

但我收到以下錯誤:

無法對包含聚合或子查詢的表達式執行聚合函數

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

有人知道我的查詢有什么問題嗎?

Does anyone know what is wrong with my query?

推薦答案

功能 SUM 對輸入采用 表達式，該表達式計算為單個數據值，而不是數據集.表達式來自 MSDN 的定義:

Function SUM takes an expression on input, which evaluates into single data value, not a dataset. Expression definition from MSDN:

是 SQL Server 數據庫引擎評估以獲得單個數據值的符號和運算符的組合.

Is a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value.

您試圖將數據集(子查詢的結果)而不是單個數據值傳遞給 SUM 函數.這是您嘗試查詢的簡化:

You trying to pass to SUM function a dataset (which is result of subquery), not a single data value. This is simplification of what you trying to query:

SELECT SUM(SELECT Number FROM SomeTable)

無效.有效的查詢將是:

It is not valid. The valid query would be:

SELECT SUM(Value) FROM SomeTable

在您的特定情況下，您似乎缺少 JOIN.您的原始邏輯將導致 Orders 表的每一行的整個 OrderShippingCosts 表的匯總.我想，應該是這樣的:

In your particular case looks like you missing JOIN. Your original logic will result in summary of entire OrderShippingCosts table for each row of Orders table. I think, it should be something like this:

SELECT
    SUM
    (
        CASE
            WHEN ord.OrderTypeID = 1 THEN ship.CostOfShippingSmallParcel 
            ELSE ship.CostOfShippingBigParcel 
        END
    ) TotalShippingCost
FROM Orders AS ord
JOIN OrderShippingCosts ship ON /* your search condition, e.g.: ord.OrderID = ship.OrderID */

順便說一句，使用保留符號作為別名、名稱等并不是一個好主意.在您的查詢中，您使用 OR 作為 Orders 表的別名.符號 OR 保留用于邏輯 or 操作.如果確實需要使用保留符號，請將其包裹在 [ 和 ] 方括號中.查看此處和此處了解更多詳情.