MySQL笔记 —— 求分组前几名问题总结（单表求topn，多表求topn，索引求topn）

只有一张表的情况下，求取某个字段值的topn问题

现在有一张员工表emp
select * from emp;

要求列出每个部门薪水前两名最高的人员名称以及薪水 top2

select * 
from emp as e1 
where 2>(select count(*) from emp e2 where e1.pid=e2.pid and e1.salary<e2.salary) 
order by pid;

模板为：

select * 
from table as a 
where num > (select count(*) 
				 from table as b 
				 where a.column1=b.column1 and a.column2=b.column2)

table是被查询的表，将表格带入时记得加上括号和别名
column1是分区字段，比如每个部门的前两名，这里的部门就是column1，用来划分比较的区域
column2是比较字段，比如每个部门薪水的前两名，这里的薪水就是column2，用来进行比较
num在这里起到截取前几名的作用，当num等于2时就是截取前两名

具体的解释可以看我之前的这篇博客MySQL笔记 —— 去重，聚合函数，concat，日期
目录里面有一个标题是 topn ，这个标题下面的内容解释了比较的原理

需要两张表连接的情况下，求topn问题

MySQL练习 —— 牛客网SQL第十二题解法（求取每个分组的最大值）
这篇博客里面就是这样的问题，员工信息被分别放在了两张表，员工表和薪水表里面，然后获取每个部门中当前员工薪水最高的相关信息
具体的解答过程可以点进去看，在这里简要的说一下思路

1. 将员工表和薪水表进行连接，然后提取需要的员工信息和部门号，作为a表
2. 将员工表和薪水表进行连接，然后提取需要的最大薪水和部门号，作为b表
3. 利用部门号将上面a，b两张表连接起来，然后判断a表中的薪水是否等于b表中的最大薪水，如果等于就显示这一行的员工信息

在不使用order by的情况下，对字段值进行排序，并且求topn问题

假设现在有一张成绩表 score1
select * from score1;

现在要求对成绩进行排序

select s1.name,s1.score,count(*) as rank
from score1 as s1,score1 as s2
where s1.score>=s2.score
group by s1.name
order by rank;

这样做，相当于相同的表取不同的别名当作两张表 s1 和 s2使用
然后拿表 s1 中的score 去和 表 s2 中的所有值进行对比，如果满足比较条件则返回一个yes，最后用count(*)统计比较成功的次数（即yes的个数），作用rank字段值进行排序

注意上面这种方式有两个要求

1. group by后面必须是主键
2. 比较的字段值里面没有重复值

如果有重复值就需要换一种写法

这里以牛客网sql题库中的第二十三题为例
有一张薪水表 salaries ，查看一下里面的数据 select * from salaries;

这时如果还像上面那样写，结果就是

select s1.emp_no,count(*) as rank
from salaries as s1,salaries as s2
where s1.salary>=s2.salary
group by emp_no
order by rank;

这样rank里面的第二名就会丢失，因为72527与表里面的四个数值进行比较时，是大于等于除了88958以外的所有数值，因此count(*)结果为3，直接跳过了2

此时应该对右表进行去重，然后再拿左表中的值依去比较

select a.emp_no,salary,t_rank from (
		select emp_no,count(*) as t_rank
		from salaries as s1,(select salary from salaries group by salary) as s2
		where s1.salary <= s2.salary
		group by emp_no) as a,salaries as b 
where a.emp_no=b.emp_no
order by t_rank;

有了排名rank字段后，想要求取第几名就变得很简单了，还是拿之前的成绩表score1来举例，求取第三名的成绩与姓名

select s1.name,s1.score,count(*) as c
from score1 as s1, score1 as s2
where s1.score>=s2.score
group by s1.name
having c=3;

利用max()聚合函数求取表中字段值的第二名

如果只是要求取字段值中排行第二的数据信息，那么可以用max聚合函数来完成
还是拿上面的score1成绩表举例

select max(score) as max 
from score1 
where score<(select max(score) from score1) 
limit 0,1;

由于select后面的字段必须在group by 里面出现，所以要再查询一次，如果score等于上面那行求出的第二名成绩，则输出姓名和成绩

select * 
from score1 
where score = (select max(score) as max from score1 where score<(select max(score) from score1));