题目:查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no
(注:输出结果以salaries.emp_no升序排序,并且请注意输出结果里面dept_no列是最后一列)
表格1–dept_manager:
表格2–salaries :
#错误的,没有确定员工表的职员ID和部门的职员ID相同
select b.emp_no,b.salary,b.from_date, b.to_date,a.dept_no from dept_manager a inner join salaries b on a.to_date=b.to_date and b.to_date='9999-01-01' GROUP BY b.emp_no,b.salary,b.from_date,b.to_date,a.dept_no ORDER BY b.emp_no
正确的1:
占用内存较大
#select b.emp_no,b.salary,b.from_date,b.to_date,a.dept_no from dept_manager a join salaries b where a.to_date='9999-01-01' and b.to_date='9999-01-01' and b.emp_no=a.emp_no ORDER BY b.emp_no
正确2:
占用内存较小,使用on条件比全部使用where条件稍好一些
#select b.*,a.dept_no from dept_manager a join salaries b on a.to_date='9999-01-01' and b.to_date='9999-01-01' where b.emp_no=a.emp_no ORDER BY b.emp_no
不知道为什么这行代码是错误的:
select b.emp_no,b.salary,b.from_date, b.to_date,a.dept_no from
dept_manager a join (select d.emp_no,d.salary,d.from_date, d.to_date from dept_manager c inner join salaries d on c.to_date=d.to_date and d.to_date='9999-01-01' GROUP BY d.emp_no,d.salary,d.from_date,d.to_date ) b where b.emp_no=a.emp_no ORDER BY b.emp_no
输出的错误结果是: