使用聚合函数的窗口函数AVG() OVER (PARTITION BY)
SELECT DISTINCT title, avg_salary FROM( SELECT *, AVG(salary) OVER (PARTITION BY title) AS avg_salary FROM titles t JOIN salaries s ON t.emp_no = s.emp_no WHERE s.to_date = '9999-01-01' ) ORDER BY avg_salary ASC;