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);
-
题解思路
-
(基础解法)
先查出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 | +-------+--------+------------+--------+
-
(自联结查询)
先对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 | +-------+--------+------------+--------+
-
(自联结查询优化版)
原理和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 | +-------+--------+------------+--------+
-
-
初浅总结,如有错误,还望指正。