16

题目：从统计出当前各个title类型对应的员工当前(to_date=‘9999-01-01')薪水对应的平均工资。结果给出title以及平均工资avg。

INNER JOIN+GROUP BY
SELECT t.title, AVG(salary) AS avg
FROM titles as t
INNER JOIN salaries as s
ON t.emp_no=s.emp_no
WHERE t.to_date='9999-01-01'
AND s.to_date='9999-01-01'
GROUP BY t.title;
注意：AVG(*)是自动命名为avg的，所以不用重命名
补充：可以在连接表的时候给的条件更多一些
SELECT t.title, AVG(salary)
FROM titles as t
INNER JOIN salaries as s
ON t.emp_no=s.emp_no
AND t.to_date='9999-01-01'
AND s.to_date='9999-01-01'
GROUP BY t.title;