题目描述
查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗 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`));
输入描述:
无
输出描述:
emp_nosalarylast_namefirst_name1000994409PeacSumant
-- 方法一
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'
-- 方法三
SELECT c.emp_no,c.salary,d.last_name,d.first_name
FROM (
SELECT a.emp_no,MIN(a.salary) AS 'salary'
FROM salaries a JOIN salaries b
ON a.salary <= b.salary
WHERE a.to_date = '9999-01-01' AND b.to_date = '9999-01-01'
GROUP BY a.emp_no
HAVING COUNT(b.emp_no) = 2
)
AS c
LEFT OUTER JOIN employees d
ON c.emp_no = d.emp_no;
