#SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
drop table if exists `employees` ;
drop table if exists `salaries` ;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
# (不能使用 order by) 思路 先求 max1 再求 <max1 的 max2
select e.emp_no,salary,last_name,first_name from salaries s left join employees e on s.emp_no = e.emp_no where salary =
(select max(salary) from (select salary from salaries where salary < (select max(salary) from salaries)) a) ;
# 根据差值
select aa.emp_no,salary,last_name,first_name from
(select *,maxs-salary dis from (select emp_no,salary,(select max(salary) from salaries ) maxs from salaries) a where maxs-salary > 0 ) aa
left join employees on aa.emp_no = employees.emp_no
where aa.dis = (select min(dis)from (select *,maxs-salary dis from (select emp_no,salary,(select max(s.salary) from salaries s) maxs from salaries) a where maxs-salary > 0) b);
-- 方法一
select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s join employees e
on s.emp_no = e.emp_no
where s.salary = -- 第三步: 将第二高工资作为查询条件
(
select max(salary) -- 第二步: 查出除了原表最高工资以外的最高工资(第二高工资)
from salaries
where salary <
(
select max(salary) -- 第一步: 查出原表最高工资
from salaries
where to_date = '9999-01-01'
)
and to_date = '9999-01-01'
)
and s.to_date = '9999-01-01'
-- 方法二
select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s join employees e
on s.emp_no = e.emp_no
where s.salary =
(
select s1.salary
from salaries s1 join salaries s2 -- 自连接查询
on s1.salary <= s2.salary
group by s1.salary -- 当s1<=s2链接并以s1.salary分组时一个s1会对应多个s2
having count(distinct s2.salary) = 2 -- (去重之后的数量就是对应的名次)
and s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
)
and s.to_date = '9999-01-01'