一个很常见的较为复杂的查询，在同一张表里求某个分组下某个属性最高的前N位记录，主要有两种方法实现，这里详细说明一下。

假设一张表记录了员工，薪水和部门Id

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

另一张表记录了部门ID和名字

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

我们希望求每个部门里薪水最高的员工和信息。

方法一 实用，简单，适用于只求每组最高/最低记录的情况

---

思路为，用employee表A与自己(B)进行联合查询，
联合条件为A.departmentid = B.departmentid，意即将同一部门的人进行全对比，
选取所有对比中的MAX(salary)，就自然会得到同一部门里面最高salary的记录
这样就可以不用group by 的取得分组里的最大记录了

select * from employee a 
where a.salary = (
select max(salary) from employee b
    where b.departmentId = a.departmentId
)

这个时候我们就可以在外层再联合department表将部门id替换为部门名字

select d.name as department, t.name as employee, t.salary
from department d,(
    select * from employee a 
    where a.salary = (
        select max(b.salary) 
        from employee b
        where b.departmentId = a.departmentId
    )
)t
where d.id = t.departmentid

不过这个嵌套结构也可以稍微进行改进，就是我们先把部门表和其中一个employee表交了，得到带部门名称的一张表，再与原始employee联合查询

select d.name as Department, a.name as Employee, a.Salary
from employee a 
join department d 
on d.id = a.departmentid
where a.salary = (
    select max(b.salary)
    from employee b
    where b.departmentId=a.departmentId)

方法二 暴力统计，可以计算每组前N位的记录，但不能只输出最高记录，意即N必须大于1

---

把employee表和自己左交，联合条件依然是a.departmentId = b.departmentId，以及，a.salary < b.salary.
这时会产生一张大表，记录了所有同部门之间的比较，
也就是说，对于某个部门最高薪水的那个人，他的记录会出现1次(因为左交一定会显示左表全部行哪怕没有匹配)，表右部分为NULL，
而薪水最低的那个人，他的记录会出现n-1次，意即，在部门里其他人都比他工资高。

得到这张表之后，只需要按a的部门分组统计count有几条记录。
最高薪水的人应该只有一条记录，
第二高的人count也只有一条记录(只有第一名比他薪水高)，
第三名count应该有两条记录(第一名和第二名)....

但是注意，此方法无法区分第一名和第二名(都是一条记录)，所以无法只选出第一名(但是可以根据salary的具体数值在输出表进行排序)

select * from employee a
join employee b
on a.departmentId = b.departmentId
and a.salary < b.salary
group by a.Id,a.departmentId
having count(*)<3