首页 > 试题广场 >

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

[编程题]获取当前薪水第二多的员工的emp_no以及其对应的薪水sal

热度指数：395740 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 32M，其他语言64M
算法知识视频讲解

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01

请你获取薪水第二多的员工的emp_no以及其对应的薪水salary，

若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序。

emp_no	salary
10002	72527

示例1

输入

drop table if exists  `salaries` ; 
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 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');

输出

10002|72527

算法知识视频讲解

文艺滴开发菇…

select emp_no, salary from salaries

where to_date = '9999-01-01' and salary = (select distinct salary from salaries order by salary desc limit 1,1)

避免了2个问题：

(1) 首先这样可以解决多个人工资相同的问题；

(2) 另外，筛选出第二多的工资时要注意distinct salary，否则不能选出第二多的工资。

编辑于 2017-10-07 10:48:38 回复(62)

crisfan

select emp_no, salary

from salaries

where salary = (select salary from salaries group by salary order by salary desc limit 1,1)

注意: 钱最多的人可能存在多个！

很久没有看到这么激烈的问题讨论！！！
4年回来了，顺便打个广告（逃）：(23条未读通知) 亲测：美团2022校招内推_招聘信息_牛客网 (nowcoder.com)

编辑于 2021-08-04 13:05:41 回复(29)

罗霄3

select emp_no,max(salary)
from salaries

where salary < (select max(salary) from salaries)

发表于 2017-07-08 17:50:06 回复(24)

nomico271

（1）首先要考虑到同一薪水有多人，假如薪水最高的有3人；薪水第二高的有2人，那么

select salary from salaries order by salary desc limit 1,1

通过上述语句limit 1,1 查出的仍然是薪水最高的第二人，所以要对salary分组查询薪水第二高的薪水（用distinct 或 group by）

select salary from salaries group by salary order by salary  desc limit 1,1

（2）正确答案为：

select emp_no, salary
from salaries
where salary = (
    select salary from salaries 
    group by salary
    order by salary desc limit 1,1
) 
and to_date = '9999-01-01'

如果薪水第二高的员工有多人，这样的答案就是有多人。

发表于 2019-05-23 23:44:13 回复(16)

小夕闭关ing

select c.emp_no, c.salary from 
(select emp_no, salary, 
dense_rank() over(order by salary desc) as rk from salaries) as c
where c.rk=2 #限定条件 第二名
limit 1 #如果有多个同值（多个第二名），只取第一个值

你们不要打啦，你们不要打啦，我看窗口函数就挺好的

发表于 2020-08-10 20:15:09 回复(17)

MiracleXY

select emp_no,salary
from salaries
where to_date = '9999-01-01'
order by salary desc limit 1,1   //salary降序排列，从1+1的位置取一个记录

发表于 2017-07-10 11:35:05 回复(7)

Mysql

Flora.201903132327831

窗口函数真好用啊

SELECT emp_no, salary

FROM
(SELECT emp_no, salary, rank() over(order by salary DESC) as rk
FROM salaries ) as a
WHERE a.rk=2

发表于 2021-10-03 21:10:52 回复(2)

瘦不到100斤不改名字

自己不太会用limit语句完善一下这个知识点 //换句话说，LIMIT n 等价于 LIMIT 0,n。 select * from table LIMIT 5,10; #返回第6-15行数据 select * from table LIMIT 5; #返回前5行 select * from table LIMIT 0,5; #返回前5行

编辑于 2019-06-08 10:07:25 回复(0)

shiago

注：如果不限定当前条件

① 在小于最高名次的数据集中查询最高的名次

select max(salary)

from salaries

where salary < (select max(salary) from salaries);

② 在除了最高名次的数据集中查询最高的名次

select max(salary)

from salaries

where salary not in (select max(salary) from salaries);

③ 返回指定的记录数

select salary

from salaries

where salary = (select distinct salary from salaries order by salary desc limit 1,1)

limit 1,1 :返回从第二个数开始的一个数

发表于 2018-11-09 11:28:29 回复(0)

大P神

别再重新爬表排序了，也别用MAX嵌套了，别人问你第100高的工资相关资料是多少难道你要嵌套99次么？

SELECT EMP_NO, SALARY FROM SALARIES S1

WHERE TO_DATE = '9999-01-01' AND
2 = (SELECT COUNT(DISTINCT SALARY)

FROM SALARIES S2 WHERE S2.SALARY >= S1.SALARY)

重点是最后两行

如果有不理解的部分，欢迎留言问我

发表于 2019-10-11 18:52:39 回复(5)

pp258

（1）SELECT emp_no, salary FROM salaries

WHERE to_date='9999-01-01' AND salary =

(SELECT salary FROM salaries GROUP BY salary ORDER BY salary DESC LIMIT 1,1);

（2）SELECT emp_no, salary FROM salaries

WHERE to_date='9999-01-01' AND salary =

(SELECT DISTINCT salary FROM salaries ORDER BY salary DESC LIMIT 1,1);

//法一使用GROUP BY 去重，法二使用DISTINCT去重，但是GROUP BY性能更好。

注意：这种类型的题要考虑薪水最多和薪水第二多的都不止一个人的情况。

发表于 2019-09-07 15:38:36 回复(0)

小海NB

/*只取一个薪水第二多的员工
SELECT 
    emp_no,
    salary
FROM
    salaries
WHERE
    to_date = '9999-01-01'
ORDER BY
    salary DESC
LIMIT 1,1
*/
/*这种写法和上面一样只能取到一个
SELECT 
    emp_no,
    MAX(salary)
FROM 
    salaries
WHERE
    salary < (SELECT MAX(salary) FROM salaries)
*/
-- 用排序方法解决

SELECT          C.emp_no, 
    C.salary 
FROM(             SELECT
            A.emp_no,
            A.salary,
            (SELECT COUNT(DISTINCT B.salary) FROM salaries B WHERE B.salary >= A.salary) AS ranking
        FROM
            salaries A
    ) C
WHERE 
    C.ranking = 2


/*还一种简便写法，不用嵌套,先执行*/ 
/*
SELECT
    emp_no,
    salary
FROM 
    salaries
WHERE
    salary = (SELECT DISTINCT salary FROM salaries ORDER BY salary DESC LIMIT 1,1)
*/

发表于 2019-07-03 09:54:46 回复(0)

华科平凡

使用limit来做

select emp_no,salary from salaries order by salary desc limit 1,1

这种形式比较简单易懂啊。

编辑于 2017-11-15 15:42:48 回复(6)

1988coding

select emp_no,salary 
from salaries
where salary = (
   	select salary from salaries
	order by salary desc
	limit 1,1
)

发表于 2017-08-29 16:24:05 回复(3)

牛客999841248号

用子查询的方式去多次排序,子连接里面去重 salary,这样能保证取所有的薪水为第二的 salary
SELECT emp_no,salary FROM salaries
WHERE salary =
(SELECT DISTINCT salary FROM salaries ORDER BY salary DESC LIMIT 1,1)
ORDER BY emp_no;

发表于 2022-07-25 01:03:12 回复(0)

Mysql

牛客803561519号

窗口函数

select t.emp_no,t.salary

from (
select *,
rank() over(order by salary desc) as t_rank
from salaries
) t
where t.t_rank=2
and t.to_date='9999-01-01'
order by t.emp_no;

发表于 2022-05-18 12:43:28 回复(0)

Mysql

一个好懒的人

使用窗口函数

使用dense_rank来排序

with temp as 
(
select *,
dense_rank() over (order by salary desc) as num
from salaries
)

select
emp_no,
salary
from temp
where num = 2

发表于 2022-05-16 22:45:00 回复(0)

Mysql

Ron1997

select
t.emp_no as emp_no,t.salary as salary
from(
select
emp_no,salary,dense_rank() over(order by salary desc) as r
from salaries)t
where t.r=2
order by t.emp_no

发表于 2022-03-19 15:31:47 回复(0)

HXYerror

drop table if exists `salaries` ;
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 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,88958,'2002-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,72527,'2002-11-01','9999-01-01');
INSERT INTO salaries VALUES(10005,43311,'2003-12-01','9999-01-01');

INSERT INTO salaries VALUES(10006,72527,'2002-11-01','2004-01-01');

包含了薪水最高有多个，薪水第二高有多个，薪水第二高但是不在职三种特殊情况，可以自己测试一下😁

我认为的

SELECT T.emp_no,T.salary
FROM (SELECT emp_no,salary,DENSE_RANK() OVER(ORDER BY salary DESC) AS rk FROM salaries WHERE to_date = '9999-01-01') AS T
WHERE T.rk = 2;

发表于 2021-04-07 22:26:07 回复(1)