我得到:“您试图执行一个查询，该查询不包含指定表达式'OrdID'作为聚合函数的一部分.我该如何绕过? [英] 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?

问题描述

我的代码如下:

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.

您试图执行不包含指定表达式'OrdID'作为聚合函数一部分的查询.

You tried to execute a query that does not include the specified expression 'OrdID' as part of an aggregate function.

是我不断收到的错误消息，无论我进行了什么更改，它都会给我这个错误.是的，我知道，它被编写为SQL-92，但是如何使它成为合法函数?

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?

推荐答案

对于几乎所有的DBMS(MySQL是我所知道的唯一例外，但可能还有其他例外)，SELECT中的每一列都不是聚合的必须位于GROUP BY子句中.对于您的查询，除了SUM():

For almost every DBMS (MySQL is the only exception I'm aware of, but there could be others), every column in a SELECT that is not aggregated needs to be in the GROUP BY clause. In the case of your query, that would be everything but the columns in the 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;  

这篇关于我得到:“您试图执行一个查询，该查询不包含指定表达式'OrdID'作为聚合函数的一部分.我该如何绕过?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！