MySQL不使用order by实现排名的三种思路总结

假定业务：

查看在职员工的薪资的第二名的员工信息

创建数据库

drop database if exists emps;
create database emps;
use emps;

create table employees(
    empid int primary key,-- 员工编号
    gender char(1) not null, -- 员工性别
	hire_date date not null -- 员工入职时间
   	);
create table salaries(
    empid int primary key, 
    salary double -- 员工薪资
    );
    
insert into employees values(10001,'m','1986-06-26');
insert into employees values(10002,'f','1985-11-21');
insert into employees values(10003,'m','1986-08-28');
insert into employees values(10004,'m','1986-12-01');
insert into salaries values(10001,88958);
insert into salaries values(10002,72527);
insert into salaries values(10003,43311);
insert into salaries values(10004,74057);

题解思路

1、(基础解法)

先查出salaries表中最高薪资,再以此为条件查出第二高的工资

查询语句如下：

select
	e.empid,e.gender,e.hire_date,s.salary
from
	employees e join salaries s 
on 
	e.empid = s.empid
where	
	s.salary=
	(
    select max(salary)from salaries 
    where 
        salary<
        (select max(salary) from salaries)
    );
-- ---------------查询结果------------ --
+-------+--------+------------+--------+
| empid | gender | hire_date  | salary |
+-------+--------+------------+--------+
| 10004 | m      | 1986-12-01 |  74057 |
+-------+--------+------------+--------+

2、(自联结查询)

先对salaries进行自联结查询，当s1<=s2链接并以s1.salary分组，此时count的值，即薪资比他高的人数，用having筛选count=2 的人，就可以得到第二高的薪资了；

查询语句如下：

select
	e.empid,e.gender,e.hire_date,s.salary
from
	employees e join salaries s 
on 
	e.empid = s.empid
where s.salary=
	(
    select 
        s1.salary
    from 
        salaries s1 join salaries s2 
    on 
        s1.salary <= s2.salary
    group by 
        s1.salary              
  	having
  	 count(distinct s2.salary) = 2
    );
-- ---------------查询结果------------ --
+-------+--------+------------+--------+
| empid | gender | hire_date  | salary |
+-------+--------+------------+--------+
| 10004 | m      | 1986-12-01 |  74057 |
+-------+--------+------------+--------+

3、(自联结查询优化版)

原理和2相同，但是代码精简了很多，上面两种是为了引出最后这种方法，在很多时候group by和order by都有其局限性，对于俺们初学者掌握这种实用性较广的思路，还是很有意义的。

select
	e.empid,e.gender,e.hire_date,s.salary
from
	employees e join salaries s 
on
    s.empid =e.empid
where
    (select count(1) from salaries where salary>=s.salary)=2；
-- ---------------查询结果------------ --
+-------+--------+------------+--------+
| empid | gender | hire_date  | salary |
+-------+--------+------------+--------+
| 10004 | m      | 1986-12-01 |  74057 |
+-------+--------+------------+--------+

初浅总结，如有错误，还望指正。

总结

到此这篇关于mysql不使用order by实现排名的三种思路的文章就介绍到这了,更多相关mysql不用order by排名内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

MySQL不使用order by实现排名的三种思路总结

相关推荐