問題描述

我有兩個(gè)表，一個(gè) Orders 表，其中包含一個(gè)用戶訂單列表和一個(gè) 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.

我正在運(yùn)行如下查詢來計(jì)算總運(yùn)費(fèi):

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

但我收到以下錯(cuò)誤:

無法對(duì)包含聚合或子查詢的表達(dá)式執(zhí)行聚合函數(shù)

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

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

Does anyone know what is wrong with my query?

推薦答案

功能 SUM 對(duì)輸入采用 表達(dá)式，該表達(dá)式計(jì)算為單個(gè)數(shù)據(jù)值，而不是數(shù)據(jù)集.表達(dá)式來自 MSDN 的定義:

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

是 SQL Server 數(shù)據(jù)庫引擎評(píng)估以獲得單個(gè)數(shù)據(jù)值的符號(hào)和運(yùn)算符的組合.

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

您試圖將數(shù)據(jù)集(子查詢的結(jié)果)而不是單個(gè)數(shù)據(jù)值傳遞給 SUM 函數(shù).這是您嘗試查詢的簡(jiǎn)化:

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.您的原始邏輯將導(dǎo)致 Orders 表的每一行的整個(gè) OrderShippingCosts 表的匯總.我想，應(yīng)該是這樣的:

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 */

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