sql刷题day2

1 获取所有部门中当前(dept_emp.to_date = '9999-01-01')员工当前(salaries.to_date='9999-01-01')薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列。

先找每个部门最大工资，再找这个部门拿这个工资的人

SELECT t1.dept_no,t1.emp_no,t1.salary
from 
(
select d.dept_no,d.emp_no,s.salary
FROM
dept_emp as d,
salaries as s
WHERE
d.emp_no=s.emp_no
AND
d.to_date='9999-01-01'
AND
s.to_date='9999-01-01'
)t1,# 连接员工和工资信息
(
select d.dept_no,d.emp_no,max(salary) as salary
FROM
dept_emp as d,
salaries as s
WHERE
d.emp_no=s.emp_no
AND
d.to_date='9999-01-01'
AND
s.to_date='9999-01-01'
GROUP BY d.dept_no
)t2 # 获取每个小组的最高工资
where t1.dept_no=t2.dept_no and t1.salary=t2.salary #查询每个小组中获取最高的工资的员工id
ORDER BY t1.dept_no

获取所有部门中当前(dept_emp.to_date = '9999-01-01')员工当前(salaries.to_date='9999-01-01')薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列。
为什么t2表不能作为结果呢？ 因为使用了group by然后默认返回第一条，即显示的员工号是分组后的默认顺序的第一条记录的员工号，但是显示的工资是分组中查询出来的最大工资，两个可能对应，也有可能不对应。所以只能先筛选出工资再去配对对应的员工号。这应该是group by的一个局限性，即分组+聚合函数只能得到分组中某一个属性（如该分组中的最大值），但是不能得到对应的一个元组（即最大值所在的元组无法确定）

2 查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗

-1 找到最大的工资
-2 找到不是最大工资的最大工资（即第二大的工资）----->这一步也不能直接的结果，像上面那题一样，聚合函数找不到对应元组，所以要再套一层
-3 找到一个拿这个工资的人

select e.emp_no,s.salary,e.last_name,e.first_name
from salaries as s,employees as e
where e.emp_no=s.emp_no
and s.to_date='9999-01-01'
and s.salary in
(
    select MAX(distinct salary) 
    from salaries as s,employees as e
    where e.emp_no=s.emp_no
    and s.to_date='9999-01-01'
    and s.salary not in
        (
        select MAX(distinct salary) 
        from salaries as s,employees as e
        where e.emp_no=s.emp_no
        and s.to_date='9999-01-01'
        )
)
)

3

两次左连接

select employees.last_name,employees.first_name,departments.dept_name
from 
employees 
left join dept_emp
on employees.emp_no=dept_emp.emp_no
left join departments 
ON departments.dept_no=dept_emp.dept_no

两次外连接 语法 阿巴阿巴