网上找解题方法，这篇比较适合我这种初学者，搬来跟大家分享。看懂后感受到了SQL的强大。
3点理解难点总结：
1、解题思路：本题要筛选各部门第3高工资（当大于该工资值的工资数为2）+第2高工资（当大于该工资值的工资数为1）+第1高工资（当大于该工资值的工资数为0），即满足大于某工资值的工资数小于3即可。
2、e1表要当做已知值来看待，每个值需要e2salary遍历一遍去比较。
3、e1.apartmentid=er.apartmentid可以将e2锁定在与e1相同的apartmentid里去比较。
链接在此，大家可以自己跑一下试试。
https://www.cnblogs.com/an5456/p/10478949.html
附上自己愚蠢的错误方法，能跑通，但是要手动输入部门名称。
select employee.name 姓名, department.name AS 部门, salary
from employee,department
where employee.departmentid=department.id
and department.name='IT'
and salary >=(
select MIN(salary) from employee,department
where employee.departmentid=department.id and department.name='IT' order by salary desc limit 0,3)
UNION
select employee.name as 姓名, department.name AS 部门, salary from employee,department
where employee.departmentid=department.id
and department.name='SALES'
and salary >=(
select MIN(salary) from employee,department
where employee.departmentid=department.id and department.name='SALES' order by salary desc limit 0,3);
网友评论