首页 > 试题广场 >

获取每个部门中薪水最高的员工相关信息

[编程题]获取每个部门中薪水最高的员工相关信息
  • 热度指数：1040527 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 32M，其他语言64M
  • 算法知识视频讲解
有一个员工表dept_emp简况如下:
emp_no
 dept_no
from_date
to_date
10001 d001
 1986-06-26 9999-01-01
10002 d001
 1996-08-03 9999-01-01
10003 d002
 1996-08-03 9999-01-01

有一个薪水表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
 92527 2001-08-02 9999-01-01

获取每个部门中薪水最高的员工相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列，以上例子输出如下:
dept_no
 emp_no
 salary
d001 10001
 88958
d002 10003
 92527
示例1

输入

drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_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 dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-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,92527,'2001-08-02','9999-01-01');

输出

dept_no|emp_no|salary
d001|10001|88958
d002|10003|92527
算法知识视频讲解
添加回答
Mysql
不知道哦_头像 不知道哦_
WITH RankedSalaries AS (
    SELECT
        a.dept_no,
        a.emp_no,
        b.salary,
        ROW_NUMBER() OVER (PARTITION BY a.dept_no ORDER BY b.salary DESC) AS rn
    FROM
        dept_emp a
    JOIN salaries b ON a.emp_no = b.emp_no
)
SELECT
    dept_no,
    emp_no,
    salary
FROM
    RankedSalaries
WHERE
    rn = 1
ORDER BY
    dept_no;

发表于 2025-08-04 20:07:18 回复(0)
Mysql
她既想找到工作也想去巴黎头像 她既想找到工作也想去巴黎 
SELECT dept_no, emp_no, salary
FROM (
    SELECT dept.dept_no, dept.emp_no, s.salary,
           RANK() OVER (PARTITION BY dept.dept_no ORDER BY s.salary DESC) AS ranking
    FROM dept_emp AS dept
    LEFT JOIN salaries AS s ON dept.emp_no = s.emp_no
) AS ranked
WHERE ranking = 1
ORDER BY dept_no;

发表于 2025-07-23 23:11:52 回复(0)
Mysql
摆小白头像 摆小白
select a.dept_no
,a.emp_no
,a.salary
from (
    select d.dept_no
    ,d.emp_no
    ,s.salary
    ,rank()over(partition by dept_no order by salary desc) as posn
    from dept_emp as d join salaries as s on d.emp_no = s.emp_no
    where d.to_date = '9999-01-01'
    and s.to_date = '9999-01-01'
) as a
where a.posn = 1
order by dept_no

我感觉最优方法是直接排序，这样不光第一，第二、第三也都能挑出来，并且逻辑上没有问题。如果排名第一的不止一个人，也可以挑选出所有排名第一的。
发表于 2025-07-22 10:55:12 回复(0)
Mysql
牛客399565409号头像 牛客399565409号
SELECT
    dept_no,
    salaries.emp_no,
    salary
FROM
    dept_emp
    JOIN salaries ON dept_emp.emp_no = salaries.emp_no
where
    (dept_no, salary) in (
        SELECT
            dept_no,
            MAX(salary)
        FROM
            dept_emp
            JOIN salaries ON dept_emp.emp_no = salaries.emp_no
        GROUP BY
            dept_no
    )
ORDER BY
    dept_no
发表于 2025-07-14 15:08:29 回复(0)
Mysql
少糖去冰的杨桃很亲切头像 少糖去冰的杨桃很亲切
select dept_no,emp_no,salary
from(
    select dept_no,dept_emp.emp_no emp_no,salary,
    rank()over(partition by dept_no order by salary desc) posn
    from dept_emp left join salaries on dept_emp.emp_no=salaries.emp_no
) rk
where rk.posn=1
order by dept_no
发表于 2025-07-12 10:07:10 回复(0)
Mysql
jacky酱头像 jacky酱
select
    dept_no,
    emp_no,
    salary
from(
    select
        d.dept_no,
        d.emp_no,
        s.salary,
        dense_rank() over (
        partition by dept_no
        order by salary desc) as rk
    from dept_emp d
join salaries s
on d.emp_no = s.emp_no
) as a
where rk = 1
order by dept_no;
发表于 2025-06-24 13:54:47 回复(0)
Mysql
不熬夜update头像 不熬夜update
select a.dept_no,c.emp_no,c.salary
from
(SELECT d.dept_no, MAX(s.salary) salary FROM salaries AS s INNER JOIN dept_emp As d ON d.emp_no = s.emp_no
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
GROUP BY d.dept_no) a join dept_emp b on a.dept_no=b.dept_no
join salaries c on b.emp_no=c.emp_no
where c.salary =a.salary
and b.to_date = '9999-01-01' AND c.to_date = '9999-01-01'
order by a.dept_no
发表于 2025-06-23 17:32:45 回复(0)
Mysql
啊哈哈哈哈搜跌死头像 啊哈哈哈哈搜跌死
select t.dept_no, t.emp_no, t.salary from (
select d.dept_no, d.emp_no, s.salary,row_number() over (partition by d.dept_no order by salary desc) as rk from dept_emp d join salaries s on d.emp_no = s.emp_no) as t where rk = 1 order by t.dept_no asc
发表于 2025-04-20 18:51:38 回复(0)
Mysql
在看数据的芒果很能干头像 在看数据的芒果很能干 
select d.dept_no,d.emp_no,s.salary
from dept_emp d join salaries s on d.emp_no = s.emp_no
where s.to_date = '9999-01-01'
and(d.dept_no,s.salary) in (
    select d2.dept_no,max(s2.salary)
    from dept_emp d2 join salaries s2 on d2.emp_no = s2.emp_no
    where s2.to_date = '9999-01-01'
    group by d2.dept_no
)
order by d.dept_no
解题思路：
1.主查询部分 
SELECT d.dept_no, s.emp_no, s.salary
FROM dept_emp d
JOIN salaries s ON d.emp_no = s.emp_no
从dept_emp和salaries两张表中，连接获取每位员工的部门和当前薪水信息
2.限定只查询当前薪水 
WHERE s.to_date = '9999-01-01'
9999-01-01是当前有效薪水的标志，说明这个工资是“现在”的
3.子查询部分（关键） 
AND (d.dept_no, s.salary) IN (
  SELECT d2.dept_no, MAX(s2.salary)
  FROM dept_emp d2
  JOIN salaries s2 ON d2.emp_no = s2.emp_no
  WHERE s2.to_date = '9999-01-01'
  GROUP BY d2.dept_no
)
 找出每个部门的“当前最高薪水”，我们再用主查询去找和这个组合相等的(dept_no, salary)，就得到了对应的员工
4.排序部分 
ORDER BY d.dept_no
让结果按部门编号升序排好


发表于 2025-04-16 22:01:35 回复(0)
Mysql
真的不会敲代码捏头像 真的不会敲代码捏
SELECT
    o.dept_no,
    e1.emp_no,
    o.salary 
FROM
    dept_emp e1
    INNER JOIN (
    SELECT
        e.dept_no,
        max( s.salary ) salary 
    FROM
        dept_emp e
        INNER JOIN salaries s ON s.emp_no = e.emp_no 
    GROUP BY
        e.dept_no 
    ) o ON e1.dept_no = o.dept_no
    INNER JOIN salaries ss ON ss.emp_no = e1.emp_no 
WHERE
    o.salary = ss.salary 
ORDER BY
    o.dept_no
发表于 2025-04-13 02:30:07 回复(0)
Mysql
kukuju头像 kukuju
-- 窗口函数
select dept_no,emp_no, salary
from(
select a.dept_no,a.emp_no, b.salary,
dense_rank()over(partition by a.dept_no order by b.salary desc) as rk
from dept_emp a
left join salaries b
on a.emp_no=b.emp_no
where a.to_date='9999-01-01'and b.to_date='9999-01-01'
) t
where rk =1
order by dept_no
发表于 2025-04-09 14:29:00 回复(0)
Mysql
梁智豪头像 梁智豪
select
    dept_no,
    a.emp_no,
    salary
from
    dept_emp as a,
    salaries as s
where
    a.emp_no = s.emp_no
    and (a.dept_no, s.salary) in (
        select
            a.dept_no,
            max(s.salary)
        from
            dept_emp as a,
            salaries as s
        where
            a.emp_no = s.emp_no
        group by
            a.dept_no
    )
发表于 2025-04-03 18:15:27 回复(0)
Mysql
Tirlayyyo头像 Tirlayyyo
自测运行和自己新建表到MySQL运行都是对的，但是就是提交的时候是错的，讨论里的很多方法提交的时候也都报错，这道题好神奇 
with t1 as
(select dept_no, s.emp_no as emp_no, salary from dept_emp d join salaries s using(emp_no)
where dept_no = 'd001'
order by salary desc
limit 1
),
t2 as
(select dept_no, s.emp_no as emp_no, salary from dept_emp d join salaries s using(emp_no)
where dept_no = 'd002'
order by salary desc
limit 1
)
select * from t1 union all select * from t2
order by dept_no

发表于 2025-04-03 01:51:00 回复(0)
Mysql
牛客611160964号头像 牛客611160964号 
with
    k as (
        select
            a.dept_no,
            max(salary) dd
        from
            dept_emp a
            left join salaries b on a.emp_no = b.emp_no
        group by
            dept_no
        order by
            dept_no
    ),
    k1 as
(select
    dept_no,
    emp_no,
    salary,
    row_number() over (partition by dept_no order by emp_no desc,salary desc) as rk
from
    salaries c
    right join k on k.dd = c.salary
order by
    dept_no)
select dept_no,emp_no,salary from k1
where rk=1



发表于 2025-03-31 17:08:31 回复(0)
Mysql
牛客398191477号头像 牛客398191477号
开窗
select dept_no,emp_no,salary from (
    select d.dept_no,s.emp_no,s.salary,
    row_number() over(partition by d.dept_no order by s.salary desc) rn
    from dept_emp d join salaries s on d.emp_no=s.emp_no
) a
where rn=1
发表于 2025-03-29 17:17:29 回复(0)
Mysql
牛客1008_头像 牛客1008_
select dept_no, emp_no, salary from
(
select
*,
dense_rank() OVER (partition by dept_no order by salary desc) as t_rank
from
    (
    select
    t1.dept_no, t1.emp_no, t2.salary
    from dept_emp t1
    left join
    salaries t2
    on t1.emp_no=t2.emp_no
    ) t12
)t3
where t_rank=1
order by dept_no
;
发表于 2025-03-17 15:10:17 回复(0)
Mysql
17719094010头像 17719094010
with t1 as (
select
t1.emp_no
,dept_no
,salary
,rank() over(partition by dept_no order by salary desc) rk
from dept_emp t1
join salaries t2
    on t1.emp_no = t2.emp_no
)
select
dept_no
,emp_no
,salary
from t1
where rk = 1
发表于 2025-03-14 15:28:53 回复(0)
Mysql
AAAAA实习战神头像 AAAAA实习战神
两种解法
第一种：这是用max聚合函数，但是！！单单使用max是错误的，要同时谁选出该最大值对应的部门编号，否则可能出现最大值薪水同时匹配多个部门的情况 
select dept_no, e.emp_no, salary 
from dept_emp e join salaries s on e.emp_no = s.emp_no
where (dept_no, salary) in (select dept_no, max(salary) 
        from dept_emp e join salaries s on e.emp_no = s.emp_no
        group by dept_no)
order by dept_no
第二种：新建一张表，用排序函数新建排名，最后筛选出排名为一的数据 
select dept_no, emp_no, salary 
from (select dept_no, e.emp_no, salary, rank()over(partition by dept_no order by salary desc) as posn
    from dept_emp e join salaries s on e.emp_no = s.emp_no) as rk
where rk.posn = 1
order by dept_no



发表于 2025-02-20 19:22:24 回复(0)
提交观点

问题信息

SQL
难度：
585条回答 66519浏览

热门推荐

通过挑战的用户

查看代码

相关试题

获取每个部门中薪水最高的员工相关信息

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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