首页 > 试题广场 >

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

[编程题]获取每个部门中薪水最高的员工相关信息
  • 热度指数：1020989 时间限制：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
啊哈哈哈哈搜跌死头像 啊哈哈哈哈搜跌死
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
牛客46253510号头像 牛客46253510号
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)
Mysql
牛客943426275号头像 牛客943426275号
其实这个把员工编号做一个聚合就好了呀
select
    a.dept_no,
    GROUP_CONCAT (sa.emp_no SEPARATOR ',') AS emp_no,
    a.maxSalary as salary
from
    salaries sa
    join (
        select
            de.dept_no,
            MAX(salary) as maxSalary
        from
            dept_emp de
            left join salaries s on de.emp_no = s.emp_no
        group by
            dept_no
    ) a on sa.salary = a.maxSalary
group by
    dept_no
order by
    dept_no

发表于 2025-02-18 12:22:41 回复(0)
Mysql
kevin_Wang333头像 kevin_Wang333
SELECT
    dept_no,
    emp_no,
    salary
FROM
    (
        SELECT
            ROW_NUMBER() OVER (PARTITION BY a.dept_no ORDER BY s.salary DESC) AS row_num,
            a.dept_no,
            a.emp_no,
            s.salary
        FROM
            dept_emp a
        JOIN salaries s ON a.emp_no = s.emp_no
    ) b
WHERE
    b.row_num = 1;
发表于 2025-02-12 15:36:32 回复(0)
Mysql
念丶木槿花开🌸头像 念丶木槿花开🌸 
select
    e.dept_no,
    e.emp_no,
    s.salary as salary
from
    dept_emp as e
    left join salaries as s on e.emp_no = s.emp_no
where
    e.emp_no in (
        select
            r.emp_no
        from
            (
                select
                    e.emp_no,
                    rank() over (
                        partition by
                            e.dept_no
                        order by
                            s.salary desc
                    ) as emp_rank
                from
                    dept_emp as e
                    left join salaries as s on e.emp_no = s.emp_no
            ) r
        where
            r.emp_rank = 1
    )
    order by e.dept_no asc
思路：利用开窗函数row_number按照部门来排序获得每个员工在他的部门下的薪资排名，row_number函数不会出现重复排名；然后再吧这个表中排名为1的员工数据筛选出来获取员工id，最后根据这个员工id查询相关数据。 
感觉上写复杂了，目前就想到的这个方法
发表于 2025-01-24 13:45:47 回复(0)
Mysql
牛客859279271号头像 牛客859279271号
不知道评论区to date 是什么鬼，估计题目改了
这题开窗比较容易理解
1. 两个表肯定是要并的，第五行并表
2. 第四行开窗，这题用rank over，如果有并列第一‘排名’都是1
3. 第六行按照部门排序，题目要求是每个部门的第一
4. 3-6行可以形成一个新表，我姑且命名为表3，第三行前缀我习惯加上
5. 1-2行再从表3里筛选，每个dept排名第一的被筛选出来，表3大概是这样
6. 第八行就是把所有排名为1的筛选出来完事


select dept_no, emp_no, salary
from
(select a.dept_no, a.emp_no, b.salary,
(rank()over(partition by dept_no order by salary desc)) as 排名
from dept_emp a join salaries b on a.emp_no=b.emp_no
order by dept_no
) as 表3
where 表3.排名=1
发表于 2025-01-16 11:21:42 回复(0)
Mysql
靠谱的托尼说又不是不能用头像 靠谱的托尼说又不是不能用 
SELECT dept_no, emp_no, salary
FROM
(
    SELECT dept_no, emp_no, salary, rank()OVER(PARTITION BY dept_no ORDER BY salary DESC) AS rk
    FROM dept_emp d
    LEFT JOIN salaries s USING (emp_no)
) AS t1
WHERE rk = 1
ORDER BY dept_no

发表于 2025-01-15 23:20:23 回复(0)
Mysql
数据分析很简单头像 数据分析很简单
select t1.dept_no, t1.emp_no,t1.salary
from
(select d.dept_no, d.emp_no,s.salary,rank() over(partition by dept_no order by salary desc) a
from dept_emp d
left join salaries s
on d.emp_no = s.emp_no) t1
where t1.a=1
order by dept_no
发表于 2025-01-08 13:23:06 回复(0)
Mysql
牛客217199488号头像 牛客217199488号
select dept_no,emp_no,salary
from (select dept_no,d.emp_no,salary,rank() over(partition by dept_no order by salary desc) as posn from dept_emp d join salaries s on d.emp_no = s.emp_no) as a
where a.posn=1
发表于 2025-01-07 01:29:36 回复(0)
提交观点

问题信息

SQL
难度：
577条回答 65478浏览

热门推荐

通过挑战的用户

查看代码

相关试题

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

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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