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表中最高薪资,再以此为条件查出第二高的工资

查询语句如下：

selectE.empId,E.gender,E.hire_date,S.salaryfromemployees E join salaries S on E.empId = S.empIdwhereS.salary=(    select max(salary)from salaries     where         salary<        (select max(salary) from salaries)    );-- ---------------查询结果------------ --+-------+--------+------------+--------+| empId | gender | hire_date  | salary |+-------+--------+------------+--------+| 10004 | M      | 1986-12-01 |  74057 |+-------+--------+------------+--------+

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

查询语句如下：

selectE.empId,E.gender,E.hire_date,S.salaryfromemployees E join salaries S on E.empId = S.empIdwhere 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 |+-------+--------+------------+--------+

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

selectE.empId,E.gender,E.hire_date,S.salaryfromemployees E join salaries S on    S.empId =E.empIdwhere    (select count(1) from salaries where salary>=S.salary)=2；-- ---------------查询结果------------ --+-------+--------+------------+--------+| empId | gender | hire_date  | salary |+-------+--------+------------+--------+| 10004 | M      | 1986-12-01 |  74057 |+-------+--------+------------+--------+

总结

更多相关文章

随机推荐