目录
只有一张表的情况下,求取某个字段值的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第十二题解法(求取每个分组的最大值)
这篇博客里面就是这样的问题,员工信息被分别放在了两张表,员工表和薪水表里面,然后获取每个部门中当前员工薪水最高的相关信息
具体的解答过程可以点进去看,在这里简要的说一下思路
- 将员工表和薪水表进行连接,然后提取需要的员工信息和部门号,作为a表
- 将员工表和薪水表进行连接,然后提取需要的最大薪水和部门号,作为b表
- 利用部门号将上面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字段值进行排序
注意上面这种方式有两个要求
- group by后面必须是主键
- 比较的字段值里面没有重复值
如果有重复值就需要换一种写法
这里以牛客网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));