问题描述
我有两个表,一个 Orders 表,其中包含一个用户订单列表和一个 OrderShippingCosts 表,其中包含基于 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.