sql - SQL Server 中的查询表达式中的语法错误（缺少运算符）

Question

我有一些 SQL 代码，当我运行它时，我得到一个错误：

SELECT AVG (DISTINCT E.salary) AS avrage
FROM aircraft AS A, certified AS C, employees AS E
WHERE A.cruisingrange > 1000 
  AND A.aid = C.aid 
  AND C.eid = E.eid

错误：

我该如何解决这个错误？

标签： sqlms-access

Answer 1

MS Access 不直接支持DISTINCT聚合函数内部的语法。这里的一种解决方法是对找到不同薪水的子查询求平均值：

SELECT AVG(salary) AS avrage
FROM
(
    SELECT DISTINCT E.salary
    FROM (aircraft AS A
    INNER JOIN certified AS C ON A.aid = C.aid)
    INNER JOIN employees AS E ON C.eid = E.eid
    WHERE A.cruisingrange > 1000
) t;

请注意，我还将您的旧式隐式连接语法转换为现代显式连接语法。这是当今编写 SQL 的首选方式。