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

2021-09-10 00:00:00 sql tsql sql-server

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

By the way, it is not a good idea to use reserved symbols as aliases, names and so on. In your query you use OR as alias for Orders table. Symbol OR is reserved for logical or operation. If you really need to use reserved symbol, wrap it into [ and ] square braces. Look here and here for more details.

相关文章