sql - SQL语法错误:“(”附近

Question

当我尝试运行此查询时：

select branch_no, max (avg_salary)
from (select allocatedto, avg (salary)
      from staff, worker
      where staff.staff_no = worker.staff_no
      group by allocatedto) 
      as branch_avg (branch_no, avg_salary);

我收到此错误：

Error: near "(": syntax error

Answer 1

select my_alias1,my_alias2 from (select col1,col2,...) as A (my_alias1,my_alias2)

上面的语法在 SQL Server中有效。

要为派生表中的列添加别名，您需要在派生表内部使用 AS。试试这个

SELECT Max (avg_salary)
FROM   (SELECT allocatedto  AS branch_no,
               Avg (salary) AS avg_salary
        FROM   staff
               INNER JOIN worker
                       ON staff.staff_no = worker.staff_no
        GROUP  BY allocatedto) AS branch_avg;

还开始使用 INNER JOIN而不是旧式逗号分隔的连接