我要做的是:2. Write a SELECT statement that answers this question: Which products have a list price that’s greater than the average list price for all products? Return the ProductName and ListPrice columns for each product. Sort the results by the ListPrice column in descending sequence.我提出的SQL代码:SELECT ProductName, ListPriceFROM ProductsWHERE Products.ListPrice > AVG(ListPrice)ORDER BY ListPrice DESC但是这给了我错误:An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.但是,当我手动计算平均值并将其作为原始int插入时:SELECT ProductName, ListPriceFROM ProductsWHERE Products.ListPrice > 841.895ORDER BY ListPrice DESC这很管用。这是为什么?为什么平均价格(标价)841.895??修复此错误的最佳方法是什么? 最佳答案 聚合函数AVG()将给出组的平均值。如果使用不带GROUP BY的聚合,则没有定义的组。这就是为什么第一次查询失败的原因。您可以使用子查询来绕过这一限制:SELECT ProductName, ListPrice FROM Products WHERE ListPrice > ( SELECT AVG(ListPrice) FROM Products ) ORDER BY ListPrice DESC;关于mysql - WHERE子句中的SQL聚合,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/28204470/
10-13 02:16