首页 > 试题广场 >

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

[编程题]获取每个部门中当前员工薪水最高的相关信息

热度指数：950750 时间限制：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	maxSalary
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');

输出

d001|10001|88958
d002|10003|92527

算法知识视频讲解

Mysql

祖拜尔实习生

select dept_no,
    emp_no,
    salary
from (
    select t1.emp_no,t1.dept_no,t2.salary,
        rank() over(partition by t1.dept_no order by t2.salary desc) rk
    from dept_emp t1 left join salaries t2
        on t1.emp_no = t2.emp_no
) t
where rk = 1

发表于今天 09:15:37 回复(0)

Mysql

TOM~JERRY

select  de.dept_no,s.emp_no,s.salary 
from 
dept_emp de  
inner join  
salaries s 
on 
de.emp_no = s.emp_no 
inner join(
    select  de.dept_no,MAX(s.salary) as maxSalary   
    from dept_emp de  
    inner join  
    salaries s 
    on de.emp_no = s.emp_no 
    GROUP BY de.dept_no) max_salary 
    on 
    s.salary = max_salary.maxSalary 
    and 
    de.dept_no=max_salary.dept_no
    order by de.dept_no

这个题也太费劲了

发表于 2024-04-26 16:20:58 回复(0)

Mysql

牛客902745608号

select de.dept_no,
       de.emp_no,
       s.salary as maxSalary
from dept_emp de
join salaries s
using(emp_no)
where salary in (select max(salary)
            from salaries 
             where dept_no = de.dept_no)

(代码错误仅存档用）有无大佬赐教，为啥只返回了一行数据

发表于 2024-04-12 19:05:42 回复(2)

Mysql

牛客491875490号

select dept_no, emp_no,salary as maxSalary from (
    select dept_emp.dept_no dept_no, dept_emp.emp_no emp_no,salary,
        dense_rank() over(partition by dept_no order by salary desc) rk
    from dept_emp join salaries
    on dept_emp.emp_no = salaries.emp_no
)tb
where rk = 1

窗口函数还是好用

编辑于 2024-04-08 18:21:04 回复(0)

Mysql

神楽恋

题目要求获取每个部门中“当前”员工薪水最高的相关信息
隐含条件to_date = '9999-01-01'这个我也是看评论区才发现漏了
然后问的是每个部门，薪水最高的相关信息，要求输出emp_no和薪水
按我的理解来，那如果有多个同样是最高薪水的，就得一并输出
了解了业务背景和目标口径，实现就是很简单的事情了，这里我是选择用分析函数

Select dept_no
    ,emp_no
    ,maxSalary
From
(Select dept_no
    ,a.emp_no emp_no
    ,salary
    ,max(salary) over(partition by dept_no) maxSalary
From 
    salaries a
Left Join
    dept_emp b
On
    a.emp_no = b.emp_no
And a.to_date = '9999-01-01'
And b.to_date = '9999-01-01')c
Where salary = maxSalary
Order By dept_no

思路是先在join后的表中增加一列各部门薪水最高是多少，然后在外面再套一层查询，只取薪水是最高薪水的员工信息

发表于 2024-04-06 20:20:08 回复(0)

Mysql

kutake

SELECT de.dept_no, de.emp_no, sa.salary AS maxSalary
FROM salaries sa
JOIN dept_emp de ON de.emp_no = sa.emp_no
WHERE sa.salary IN (
        SELECT MAX(s.salary)
        FROM salaries s
        JOIN dept_emp d ON d.emp_no = s.emp_no
        WHERE d.dept_no = de.dept_no  -- 添加部门号条件保证每个部门只返回一个最高薪水员工
        GROUP BY d.dept_no
    )
ORDER BY de.dept_no ASC;

正确答案

编辑于 2024-03-30 11:49:24 回复(0)

Mysql

牛客884909151号

遇事不决,直接开窗

select dept_no,emp_no,salary from

(

select de.emp_no,de.dept_no,ss.salary, dense_rank()over(partition by dept_no order by salary desc) as rk

from

dept_emp de

left join

salaries ss

on de.emp_no = ss.emp_no

) t1

where t1.rk = 1 order by dept_no

发表于 2024-03-28 14:35:47 回复(0)

Mysql

牛客642618423号

窗口函数方法：

with a as(
    select dept_no, de.emp_no, salary,
    max(salary) over(partition by dept_no order by salary desc) as maxSalary
    from dept_emp de
    join salaries s
    on de.emp_no = s.emp_no
)

select dept_no, emp_no, salary
from a
where salary = maxSalary

发表于 2024-03-21 15:58:12 回复(0)

Mysql

牛客397275294号

select dept_no,emp_no,salary

from

(select d.dept_no,d.emp_no,s.salary,

dense_rank() over(partition by d.dept_no order by s.salary desc) r

from dept_emp d

inner join salaries s

on d.emp_no = s.emp_no ) t

where r=1

order by dept_no;

发表于 2024-03-20 20:59:55 回复(0)

Mysql

坚定的小黄鸭

SELECT
    emp1.dept_no,
    emp1.emp_no,
    sal1.salary
FROM
    dept_emp emp1
    LEFT JOIN salaries sal1 ON emp1.emp_no = sal1.emp_no
WHERE
    ( emp1.dept_no, sal1.salary )in (
    SELECT
        dept_no,
        MAX( sal.salary )
    FROM
        dept_emp emp
        LEFT JOIN salaries sal ON emp.emp_no = sal.emp_no
    GROUP BY
        dept_no
    )
order by emp1.dept_no

先根据where之后的子查询查出所有部门和对应的最高工资,之后连表查询,找出该部门下最高工资对应的人员,
联合表之后的查询条件一定要使用这两个字段 emp1.dept_no, sal1.salary,这两个字段都在子查询中才不会导致数据重复

编辑于 2024-03-19 17:41:19 回复(0)

Mysql

老小羊肖恩

-- 生成两张中间表
-- stmp: 包含 员工编号，员工工资，部门编号
-- tmp: 包含 部门编号和部门最大的薪资
-- 连接stmp和tmp：    stmp.salary = tmp.salary and stmp.dept_no = tmp.dept_no

select
    tmp.dept_no,
    stmp.emp_no,
    stmp.salary as maxSalary
from
    (
        select s.emp_no, s.salary, de.dept_no
        from salaries s
        left join dept_emp de 
        on s.emp_no = de.emp_no
    ) stmp,
    (
        select de.dept_no, max(s.salary) as salary
        from salaries s
        left join dept_emp de 
        on s.emp_no = de.emp_no
        group by de.dept_no
    ) tmp
where
    stmp.salary = tmp.salary 
    and stmp.dept_no = tmp.dept_no
order by
    tmp.dept_no

编辑于 2024-03-16 23:31:24 回复(0)

Mysql

代数学与火焰

用窗口函数比较清晰

select new.dept_no,new.emp_no,new.salary maxSalary from (select de.dept_no,de.emp_no,sa.salary,rank()over (partition by de.dept_no order by sa.salary desc) as rk from (dept_emp de join salaries sa on de.emp_no=sa.emp_no)) as new

where new.rk=1

order by new.dept_no

编辑于 2024-03-13 15:24:27 回复(0)

Mysql

椰椰芝麻

select d.dept_no,d.emp_no, s.salary as maxSalary

from dept_emp as d

inner join salaries as s

on d.emp_no = s.emp_no

where s.salary in (select max(s2.salary)

from dept_emp as d2

inner join salaries as s2

on d2.emp_no = s2.emp_no

group by d2.dept_no

having d2.dept_no = d.dept_no)

order by d.dept_no;

发表于 2024-03-10 14:41:16 回复(0)

Mysql

Mr.小美吖

with t as (
select t1.dept_no,t2.emp_no as emp_no,t2.salary,rank() over(partition by dept_no order by salary desc) as ranking
from dept_emp as t1
inner join salaries as t2
on t1.emp_no = t2.emp_no
)

select dept_no,emp_no,salary from t
where ranking = 1
order by dept_no;

编辑于 2024-03-08 18:44:38 回复(0)

Mysql

牛客883242838号

t1临时表里拿到每个部门最高的工资这里每个部门最高工资可以对应的是多个人

t2临时表里就是简单的进行关联

最后一步查询就是与原表关联拿到部门最高工资对应的员工id

PS: 单独一个 "工资"列不能关联上员工id 必须还得加上部门 id

当前这个条件加上 to_date= "9999-01-01" 就行了不做考虑

with t1 as (
    select
        dept_no,
        max(salary) maxSalary
    from 
        dept_emp a left join salaries b on a.emp_no =b.emp_no
    group by dept_no
),
t2 as (
    select 
        dept_no,
        a.emp_no,
        salary 
    from
         dept_emp a left join salaries b on a.emp_no =b.emp_no
)
select  t1.dept_no ,emp_no,maxSalary from t1 left join  t2 on t1.maxSalary =t2.salary and t1.dept_no =t2.dept_no order by dept_no

发表于 2024-03-04 19:46:31 回复(0)