将 SQL SUM 与包含内部 SELECT 的 Case 语句一起使用

问题描述

我有两个表，一个 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 操作.如果确实需要使用保留符号，请将其包裹在 [ 和 ] 方括号中.查看此处和此处了解更多详情.