"Select is not valid at this position for this server version, expecting '(' WITH"

Question

我当前正试图运行SQL查询，但收到错误："Select is not valid at this position for this server version, expecting '(' WITH"。我读过其他有同样错误的帖子，似乎与子查询中缺少逗号有关。据我所知，我没有遗漏任何逗号。

SELECT employees.Location, AVG(employees.salaries) as AvgLocSal
FROM employees,
(SELECT employees.Location, AVG(employees.salaries) as b
FROM employees
GROUP BY employees.Location) as otherSal
GROUP BY employees.Location
HAVING AvgLocSal >= all(otherSal.b);

简言之，我正在比较各个地点的平均工资，并找到工资最高的地点。

我可以通过将子查询放在HAVING子句中(如下所示(来成功地做到这一点，但我真的不确定上面的查询为什么会出现错误。

SELECT Location, AVG(salaries) as AvgLocSal
FROM employees
GROUP BY Location
HAVING AvgLocSal >= all(
SELECT AVG(salaries)
FROM employees
GROUP BY Location
);

我可以简单地按工资排序，然后将其限制为1，但如果两个工资相同，则无法得到所需的结果。

任何想法都将不胜感激。

谢谢，纳什

Answer 1

正如您所提到的，您希望在各个地点平均工资，并找到工资最高的地点。以下内容将为您提供预期输出。

SELECT Location, AVG(salaries)
FROM employees
GROUP BY Location
ORDER BY 2 DESC
LIMIT 1;    

或者：

SELECT MAX(avgSalary.salaries)
FROM (SELECT AVG(salaries) AS 'salaries' FROM employees GROUP BY Location) AS avgSalary;