SQL 我得到：“您试图执行不包含指定表达式‘OrdID’作为聚合函数一部分的查询。我该如何绕过？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15710741/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
I am getting: "You tried to execute a query that does not include the specified expression 'OrdID' as part of an aggregate function. How do I bypass?
提问by user2225611
My code is as follows:
我的代码如下：
SELECT Last, OrderLine.OrdID, OrdDate, SUM(Price*Qty) AS total_price
FROM ((Cus INNER JOIN Orders ON Cus.CID=Orders.CID)
INNER JOIN OrderLine
ON Orders.OrdID=OrderLine.OrdID)
INNER JOIN ProdFabric
ON OrderLine.PrID=ProdFabric.PrID
AND OrderLine.Fabric=ProdFabric.Fabric
GROUP BY Last
ORDER BY Last DESC, OrderLine.OrdID DESC;
This code has been answered before, but vaguely. I was wondering where I am going wrong.
之前已经回答过这段代码，但含糊其辞。我想知道我哪里出错了。
You tried to execute a query that does not include the specified expression 'OrdID' as part of an aggregate function.
您尝试执行的查询不包含指定的表达式“OrdID”作为聚合函数的一部分。
Is the error message I keep getting, no matter what I change, it gives me this error. Yes I know, it is written as SQL-92, but how do I make this a legal function?
是我不断收到的错误消息，无论我更改什么，它都会给我这个错误。是的，我知道，它被写成 SQL-92，但我如何使它成为一个合法的函数？
回答by Ken White
For almost every DBMS (MySQL is the only exception I'm aware of, but there could be others), every column in a SELECTthat is not aggregated needs to be in the GROUP BYclause. In the case of your query, that would be everything but the columns in the SUM():
对于几乎每个 DBMS（MySQL 是我所知道的唯一例外，但可能还有其他例外）， aSELECT中未聚合的每一列都需要在GROUP BY子句中。在您的查询的情况下，这将是除以下列之外的所有内容SUM()：
SELECT Last, OrderLine.OrdID, OrdDate, SUM(Price*Qty) AS total_price
...
GROUP BY Last, OrderLine.OrdID, OrdDate
ORDER BY Last DESC, OrderLine.OrdID DESC;
回答by PM 77-1
If you haveto keep your GROUP BYintact (and notto add non-agreggated fields to the list) then you need to decide which values you will want for OrderLine.OrdIDand OrdDate. For example, you may chose to have MAXor MINof these values.
如果您必须保持GROUP BY完整（而不是将非聚合字段添加到列表中），那么您需要决定您需要哪些值OrderLine.OrdID和OrdDate。例如，您可以选择具有这些值的MAX或MIN。
So it's either as berniesuggested GROUP BY Last, OrderLine.OrdID, OrdDateor something like this (if it makes sense for your business logic):
所以它要么像伯尼建议的那样，要么GROUP BY Last, OrderLine.OrdID, OrdDate像这样（如果它对你的业务逻辑有意义）：
SELECT Last, MAX(OrderLine.OrdID), MAX(OrdDate), SUM(Price*Qty) AS total_price
SELECT Last, MAX(OrderLine.OrdID), MAX(OrdDate), SUM(Price*Qty) AS total_price
