SQL 分组内取前几名的问题

查找 部门工资前三高的所有员工

比如这题，找每个部门的工资前三名，那么先在子查询中用Employee和自己做连接，
连接条件是【部门相同但是工资比我高】，那么接下来按照having count(Salary) <= 2
来筛选的原理是：如果【跟我一个部门而且工资比我高的人数】不超过2个，那么我一定是部门工资前三，
这样内层查询可以查询出所有符合要求的员工ID，接下来外层查询就简单了。```

# 内层查询，找前三高工资员工
select e1.Id
from Employee e1 left join Employee e2
# 和我（e1）在同一部门的
on e1.DepartmentId=e2.DepartmentId
# 且工资比我高的
and e1.Salary<e2.Salary
# 不超过2个
group by Id
having count(distinct e2.Salary)<=2

# 外层查询
select d.Name as Department,e.Name as Employee,e.Salary as Salary
from Employee e left join Department d
on  e.DepartmentId=d.Id
where e.Id in (
    "内层Id 结果集"
    )
    and e.DepartmentId in(
        select Id
        from Department
    )
order by d.Id asc,e.Salary desc

# 外层查询
select d.Name as Department,e.Name as Employee,e.Salary as Salary
from Employee e left join Department d
on  e.DepartmentId=d.Id

where e.Id in (
    # 内层查询，找前三高工资员工
select e1.Id
from Employee e1 left join Employee e2
# 和我（e1）在同一部门的
on e1.DepartmentId=e2.DepartmentId
# 且工资比我高的
and e1.Salary<e2.Salary
# 不超过2个
group by Id
having count(distinct e2.Salary)<=2
    )
    and e.DepartmentId in(
        select Id
        from Department
    )
order by d.Id asc,e.Salary desc