首页 > 试题广场 >

获取当前薪水第二多的员工的emp_no以及其对应的薪水sal

[编程题]获取当前薪水第二多的员工的emp_no以及其对应的薪水sal
  • 热度指数：500537 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 32M，其他语言64M
  • 算法知识视频讲解
有一个员工表employees简况如下:
emp_no 
 birth_date 
 first_name 
 last_name 
 gender hire_date 
10001
 1953-09-02
 Georgi     
 Facello   
  M 1986-06-26
10002
 1964-06-02
 Bezalel    
 Simmel    
  F 1985-11-21
10003  
 1959-12-03
 Parto      
 Bamford   
  M 1986-08-28
10004  
 1954-05-01
 Chirstian  
 Koblick   
  M 1986-12-01


有一个薪水表salaries简况如下:
emp_no 
 salary
 from_date 
 to_date
10001
 88958 2002-06-26
 9999-01-01
10002 72527 2001-08-02
 9999-01-01
10003
 43311 2001-12-01
 9999-01-01
10004 74057 2001-11-27 9999-01-01

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:
（温馨提示:sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时，select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。如果使用非group by的列名，sqlite的结果和mysql 可能不一样)
emp_no 
 salary
 last_name first_name
10004 74057 Koblick Chirstian

示例1

输入

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');

输出

10004|74057|Koblick|Chirstian
算法知识视频讲解
添加回答
ciphersaw头像 ciphersaw
本题做法很多，主要思想为多层SELECT嵌套与MAX()函数结合
1、先利用MAX()函数找出salaries中当前薪水最高者，即SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01'
2、再利用INNER JOIN连接employees与salaries表，限定条件为【同一员工】e.emp_no = s.emp_no、【当前】s.to_date = '9999-01-01'与【非薪水最高】s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
3、在以上限制条件下找薪水最高者，即为所有员工薪水的次高者 
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name 
FROM employees AS e INNER JOIN salaries AS s 
ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01' 
AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')

发表于 2017-07-13 12:25:45 回复(69)
更多回答
(≧∇≦)三玖头像 (≧∇≦)三玖 
解法1 max 任意相同工资人数
select e.emp_no,s.salary,e.last_name,e.first_name
from
employees e
join 
salaries s on e.emp_no=s.emp_no 
and  s.to_date='9999-01-01'
and 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'
                )

第二种 通用型可以求任意第几高，并且可以求多个形同工资
select e.emp_no,s.salary,e.last_name,e.first_name
from
employees e
join 
salaries s on e.emp_no=s.emp_no 
and  s.to_date='9999-01-01'
and s.salary = 
(
     select s1.salary
     from 
     salaries s1
     join
     salaries s2 on s1.salary<=s2.salary 
     and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
     group by s1.salary
     having count(distinct s2.salary)=2
 )


发表于 2019-09-02 14:59:14 回复(109)
uu.king头像 uu.king
select e.emp_no,max(s.salary),e.last_name,e.first_name from employees e, salaries s
where s.salary < (select max(salary) from salaries)
and s.to_date="9999-01-01" and e.emp_no=s.emp_no;
最简洁明了，一次通过！！
发表于 2017-09-27 22:29:19 回复(24)
心之丶所向头像 心之丶所向
薪水第二多，意思就是只有一个人比他多，这样只要用salary表内连接，再分组，组内条数等于1的那条记录，就正好是第二多。如果要找第三多，那就让having count(*)=2就行了，只有两个人比那个人多。。还可扩展呢~
不过如果，有两个人比那个人多，这两个人的薪水值相同的话，还得用一下distinct关键字消除。
select e.emp_no,s3.salary,e.last_name,e.first_name
from employees e
inner join(
select s1.emp_no,s1.salary
from salaries s1
inner join salaries s2
on s1.salary < s2.salary
where s1.to_date = '9999-01-01' and s2.to_date = '9999-01-01'
group by s1.emp_no
having count(*) = 1
) s3
on e.emp_no = s3.emp_no;
编辑于 2018-01-23 12:48:09 回复(18)
不服苏头像 不服苏

方法1

思路

通过将表 employees 和表 salaries 内连接查询就可得到输出样例中的结果，重点在于如何在不通过 order by 排序的情况下得到薪水第二多的员工记录

步骤 1

先找出 salaries 表中薪水最多的记录

select max(salary) max_salary from salaries;
步骤 2

在 where 条件中过滤掉步骤 1 中得到的薪水最多的记录，并重复 1 次步骤 1，这样就能得到薪水第二多的记录

select max(salary) secondary_salary
  from salaries
 where salary != (select max(salary) max_salary from salaries);
步骤 3

将表 employees 和表 salaries 进行联合查询，并且将薪水第二多的薪水作为过滤条件

select b.emp_no,
       b.salary,
       a.last_name,
       a.first_name
  from employees a inner join
       salaries  b on a.emp_no = b.emp_no
 where b.salary in (select max(salary) max_ssecondary_salaryalary_2
                      from salaries
                     where salary != (select max(salary) max_salary from salaries)
                    )

方法2

思路

将表 salaries 与表 salaries 进行笛卡尔连接，并以薪水作为过滤条件来得到每个员工从高到低的薪水排名

步骤 1

将表 salaries 与表 salaries 进行笛卡尔连接，并以薪水作为过滤条件来得到每个员工从高到低的薪水排名，以及

select b.emp_no,
       max(b.salary) salary,
       count(*) rank_salary_desc
  from salaries b cross join 
       salaries c on b.salary <= c.salary
 group by b.emp_no
步骤 2

得到薪水第二多的员工编号及薪水

select b.emp_no,
       max(b.salary) salary,
       count(*) rank_salary_desc
  from salaries b cross join 
       salaries c on b.salary <= c.salary
 group by b.emp_no
having rank_salary_desc = 2
步骤 3

将步骤 2 中得到的薪水第二多的记录与员工信息表 employees 进行内连接以获取员工姓名信息

  select d.emp_no,
         d.salary,
         a.last_name,
         a.first_name
   from employees a inner join
        (select b.emp_no,
               max(b.salary) salary,
               count(*) rank_salary_desc
          from salaries b cross join 
               salaries c on b.salary <= c.salary
         group by b.emp_no
        having rank_salary_desc = 2
        ) d on a.emp_no = d.emp_no
编辑于 2021-02-08 11:26:59 回复(5)
小Andy头像 小Andy
select employees.emp_no,salary,last_name,first_name 
from employees inner join salaries on employees.emp_no = salaries.emp_no 
where to_date = '9999-01-01' 
and 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'
);
发表于 2017-07-16 17:15:57 回复(11)
yoguuu头像 yoguuu 
#使用rank()函数
select emp_no,salary ,last_name,first_name
from (
    select e.emp_no,s.salary,e.last_name,e.first_name,
    rank() over (order by salary desc) as rk
    from employees e
    join salaries s on e.emp_no=s.emp_no
    where s.to_date='9999-01-01'
)a
where rk=2


发表于 2020-05-12 09:36:28 回复(7)
码不停手1头像 码不停手1

select e.emp_no, Max(s.salary) as salary, e.last_name, e.first_name

from employees e,salaries s

where e.emp_no=s.emp_no

and s.to_date='9999-01-01'

and s.salary!= (select Max(salary) from salaries where to_date='9999-01-01')

主要是两次max的嵌套使用

发表于 2017-07-28 15:14:35 回复(11)
rrrrrrrrrrrrrrrr头像 rrrrrrrrrrrrrrrr
不用max的方法：
借鉴了mysql分组取每组前几条记录的方法，优势在于这种方法可以取第任意多薪水的员工：

https://blog.csdn.net/zhanghongju/article/details/8450887


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
and s.to_date='9999-01-01'
and s.emp_no=(select s1.emp_no from salaries s1 join salaries s2 on s1.salary<=s2.salary 
              and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
              group by s1.emp_no having count(1) = 2);
发表于 2018-04-13 22:10:29 回复(10)
星曌云天头像 星曌云天
最高薪水：
select max(salary) from salaries where to_date='9999-01-01'
除去最高薪水： 
select * from salaries where salary!=(
             select max(salary) from salaries where to_date='9999-01-01'
        ) 
剩下的最高薪水： 
select max(salary) from (
        select * from salaries where salary!=(
            select max(salary) from salaries where to_date='9999-01-01'
        )
    ) where to_date='9999-01-01'
最后的完整代码：
select s.emp_no,s.salary,e.last_name,e.first_name from salaries s,employees e 
where s.emp_no=e.emp_no 
and s.to_date='9999-01-01' 
and s.salary=(
    select max(salary) from (
        select * from salaries where salary!=(
            select max(salary) from salaries where to_date='9999-01-01'
        )
    ) where to_date='9999-01-01'
);

发表于 2017-10-09 15:16:03 回复(1)
Mysql
牛客716609217号头像 牛客716609217号 
select e.emp_no,salary,last_name,first_name
from employees e
JOIN salaries s
on e.emp_no=s.emp_no
where (SELECT count(*) from salaries ss where s.salary<ss.salary)=1
发表于 2021-09-25 20:55:48 回复(3)
Mysql
together"头像 together" 
-- 方法一: 连续用两次MAX()函数查出第二大的salary, 将其作为外层连接查询的过滤条件
SELECT e.emp_no, s.salary, e.last_name, e.first_name
FROM employees e
JOIN salaries s
ON e.emp_no = s.emp_no 
WHERE s.to_date = '9999-01-01' AND s.salary =
        (SELECT MAX(salary)
         FROM salaries
         WHERE to_date = '9999-01-01' AND salary < (SELECT MAX(salary) 
                                                    FROM salaries 
                                                    WHERE to_date = '9999-01-01'));

-- 方法二: 利用自连接和COUNT()函数找出第二大的salary, 将其作为外层连接查询的过滤条件
-- 该方法更加通用, 可以拓展到求薪水第n多的员工, 应该熟练掌握!!
SELECT e.emp_no, s.salary, e.last_name, e.first_name
FROM employees e
JOIN salaries s
ON e.emp_no = s.emp_no 
WHERE s.to_date = '9999-01-01' AND s.salary = 
        (SELECT s1.salary
         FROM salaries s1
         JOIN salaries s2 ON s1.salary <= s2.salary AND s1.to_date = '9999-01-01' AND s2.to_date = '9999-01-01'
         GROUP BY s1.salary
         HAVING COUNT(DISTINCT s2.salary) = 2);

发表于 2021-08-04 01:02:01 回复(0)
Mysql
en_answer头像 en_answer 
SELECT
	s1.emp_no,
	s1.salary,
	e.last_name,
	e.first_name
FROM
	salaries s1,
	employees e
WHERE
	(
		SELECT
			COUNT(DISTINCT salary)
		FROM
			salaries s2
		WHERE
			s1.salary < s2.salary
	) = 1
AND s1.emp_no = e.emp_no;

发表于 2021-07-21 16:42:35 回复(1)
LH7miamiaoao头像 LH7miamiaoao
select a.emp_no,b.salary,a.last_name,a.first_name
from employees a
inner join salaries b
on a.emp_no=b.emp_no and b.to_date='9999-01-01'
where 1=(select count(distinct c.salary) from salaries c where b.salary<c.salary);
发表于 2018-04-11 16:37:53 回复(2)
做自己的太阳头像 做自己的太阳
SELECT e.emp_no , s.salary , e.last_name , e.first_name from employees e
INNER JOIN salaries  s
ON e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
AND s.salary =
(SELECT salary from salaries where to_date = '9999-01-01' 
GROUP BY salary DESC LIMIT 1,1)
如果第二高工资有多个的话就直接先找出第二高工资是多少，然后直接查找就行了，不需要用到max函数
发表于 2017-12-21 10:58:38 回复(5)
Mysql
牛客198698999号头像 牛客198698999号
思路：
1.：先找出最高的薪水值（最高的薪水）：select max(salary) from salaries，
2： 然后找出不满足最大薪水值的其它数据，从不满足最大薪水值的其它数据中找到最大的薪水值（第二大薪水的值）：select distinct max(salary) from salaries where salary not in (select max(salary) from salaries))，
3.：然后找出满足第二薪水值的人的数据：salary = (select salary from salaries
          where salary = 
(select distinct max(salary) from salaries where salary not in (select max(salary) from salaries)));

最后的完整SQL语句：
select employees.emp_no,salary,last_name,first_name 
from employees,salaries where employees.emp_no = salaries.emp_no and
salary = (select salary from salaries
          where salary = 
(select distinct max(salary) from salaries where salary not in (select max(salary) from salaries)));

上述SQL语句同时也能满足 有多个最大，第二大薪水的情况！！！
发表于 2022-03-16 16:36:43 回复(0)
Mysql
牛客262527258号头像 牛客262527258号
真诚发问，明明没涉及到日期为什么都要加上日期啊？
发表于 2021-09-06 15:44:30 回复(1)
Wake21头像 Wake21
本题的关键是找出排名第二多的薪水的值是多少，然后根据这个薪水值找出对应的员工信息。
下面是找出排名第二多的薪水的值的两种方法。
第一种方法：
（1）检索出排名第一的薪水的值，将其过滤掉；
（2）在剩下的薪水里，检索薪水排名第一的薪水值即为排名第二多的薪水。 
SELECT e.emp_no,s.salary,e.last_name,e.first_name
FROM employees e INNER JOIN salaries s
ON e.emp_no = s.emp_no
AND s.salary = (
SELECT MAX(salary) FROM salaries
WHERE salary NOT IN (
SELECT MAX(salary) FROM salaries));
-- 或
SELECT e.emp_no,s.salary,e.last_name,e.first_name
FROM employees e INNER JOIN salaries s
ON e.emp_no = s.emp_no
AND s.salary = (
SELECT MAX(salary) FROM salaries
WHERE salary < (
SELECT MAX(salary) FROM salaries));

-- 注意，以下代码运行后返回的结果是错误的，薪水是排名第二的薪水，但跟返回的员工信息不对应
SELECT e.emp_no,MAX(s.salary) AS salary,e.last_name,e.first_name
FROM employees e INNER JOIN salaries s
ON e.emp_no = s.emp_no
AND s.salary NOT IN (
SELECT MAX(salary) FROM salaries);
第二种方法：（这种方法适用于找出薪水排名为第任意名的员工信息）
使用自联结，自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。有时候处理联结远比处理子查询快得多，可以试试两种方法以确定哪种性能更好。 
SELECT e.emp_no,s.salary,e.last_name,e.first_name
FROM employees e INNER JOIN salaries s
ON e.emp_no = s.emp_no
AND s.salary = (
SELECT s1.salary 
FROM salaries AS s1,salaries AS s2
WHERE s1.salary <= s2.salary
GROUP BY s1.salary
HAVING COUNT(DISTINCT s2.salary) = 2);

下面详细讲解这种方法的思路，表salaries各行数据如下： 
SELECT s1.salary AS salary1,s2.salary AS salary2
FROM salaries AS s1,salaries AS s2
WHERE s1.salary <= s2.salary
ORDER BY s1.salary,s2.salary;
-- 为了方便我们看懂，我用了ORDER BY排序

SELECT s1.salary,COUNT(s2.salary)
FROM salaries AS s1,salaries AS s2
WHERE s1.salary <= s2.salary
GROUP BY s1.salary;

SELECT s1.salary,COUNT(DISTINCT s2.salary)
FROM salaries AS s1,salaries AS s2
WHERE s1.salary <= s2.salary
GROUP BY s1.salary;

以上三条语句的结果如下：
在联结两个表时，实际上做的是将第一个表中的每一行与第二个表中的每一行配对，WHERE子句作为过滤条件，它只包含那些匹配给定条件的行。
由没有联结条件的表关系返回的结果称为笛卡尔积，检索出的行的数目是第一个表的行数与第二个表的行数的乘积。
第一条语句使用自联结，过滤条件为s1.salary <= s2.salary ，每一个s1.salary匹配给定条件的s2.salary，返回的结果有29行。salaries表中薪水为43311的员工有两个，表s1的s1.salary=43311时， 匹配表s2的所有行，7行，s2.salary的值分别为43311、43311、725527、74057、88070、88958、94692，表s1的s1.salary=43311时，匹配表s2的所有行，7行，s2.salary的值分别为43311、43311、725527、74057、88070、88958、94692，表1的s1.salary=72527时，匹配表s2的5行，s2.salary的值分别为725527、74057、88070、88958、94692，...，由此返回第一条语句的结果。
第二条语句在第一条语句的基础上加了按照s1.salary分组，对每个组里s2.salary计数，理解了第一条语句的结果后，分组结果便很好 理解了 。
第三条语句与第二条不同，使用了DISTINCT关键字，按照s1.salary分组，对每个组里值不同的s2.salary计数，理解了前两条语句的结果后，第三条语句的分组结果便很好理解了。
理解了以上 三条 语句 的 结果 ，便可轻松理解本题使用自联结找出排名第二多的薪水是多少的思路：使用过滤条件为s1.salary <= s2.salary的自联结，按照s1.salary 分组，对每个组里值不同的s2.salary计数，找出计数值（即组里值不同的s2.salary的个数）等于2的组对应的s1.salary，即为排名第二多的薪水的值。
注意：第二种方法（使用带过滤条件的自联结，GROUP BY-分组，HAVING-过滤分组）也适用于找出薪水排名为第任意名的员工信息，只需更改HAVING COUNT(DISTINCT s2.salary)=2这个过滤条件。找出薪水排名第三的员工信息，HAVING COUNT(DISTINCT s2.salary)=3；找出薪水排名第五的员工信息，HAVING COUNT(DISTINCT s2.salary)=5。


发表于 2021-06-23 14:30:38 回复(2)
我不想静静～头像 我不想静静～ 
select e.emp_no, s.salary, e.last_name, e.first_name from employees as e 
join salaries as s
on e.emp_no = s.emp_no 
where s.to_date='9999-01-01'
and 1= (select count(distinct salary) from salaries s1
        where s1.salary>s.salary 
        and s.to_date='9999-01-01' and s1.to_date='9999-01-01')

从上道题 一位同学的做法参考的 拓展性强
编辑于 2020-06-15 10:30:16 回复(0)
BinSTD头像 BinSTD
select e.emp_no,max(s.salary), e.last_name, e.first_name from employees as e inner join salaries as s on e.emp_no = s.emp_no where s.salary < (select max(salary) from salaries) and e.to_date = '9999-01-01' and s.to_date = '9999-01-01'
编辑于 2019-03-29 09:46:44 回复(2)
提交观点

问题信息

难度：
898条回答 43231浏览

热门推荐

通过挑战的用户

查看代码

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号