MySQL不用order by实现排名的3种思路

MySQL不用order by实现排名的3种思路

• 假定业务：

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

• 创建数据库

  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 |
  +-------+--------+------------+--------+
  

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