首页 > 试题广场 >

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

[编程题]获取每个部门中薪水最高的员工相关信息
  • 热度指数：993697 时间限制：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');

输出

dept_no|emp_no|salary
d001|10001|88958
d002|10003|92527
算法知识视频讲解
添加回答
Mysql
牛客819243618号头像 牛客819243618号
select dept_no,emp_no,salary from (
select a.dept_no, a.emp_no,dense_rank() over (partition by dept_no order by salary desc) maxSalary,salary from dept_emp a join salaries b on a.emp_no = b.emp_no
) as c where c.maxSalary <= 1 ;
发表于 2024-11-12 15:52:40 回复(0)
Mysql
灵神头像 灵神
1.题意要求输出字段dept_no,emp_no,maxSalary，其中dept_emp表有dept_no,emp_no，salaries表有emp_no,salary，所以使用emp_no为连接条件。
2.假设用分组group by求maxSalary，那么将获取不到emp_no，所以使用rank()over开窗函数，以部门为分区条件，对salary降序排序
3.套一层子查询，获取到排名为1的记录即可，最后对dept_no升序排序
4.这道题，两个表都是拉链表，其实如果考虑更深入点的话，应当使用拉链表全量切片取数的方法做题，但是如果只想做简单点的，就只需要达到1、2、3这三个步骤就行了 
select dept_no,
       emp_no,
       salary maxSalary
from (
    select d.dept_no,
           d.emp_no,
           s.salary,
           rank()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
)t1
where rn=1
order by dept_no;
发表于 2024-11-02 11:35:59 回复(0)
Mysql
牛客187305673号头像 牛客187305673号
select
    dept_emp.dept_no,
    dept_emp.emp_no,
    salaries.salary
from
    (select
        dept_no,
        MAX(salary) as max_salary
    from
        dept_emp
        inner join salaries on dept_emp.emp_no = salaries.emp_no
    group by
        dept_no) as subquery
inner join
    dept_emp on subquery.dept_no = dept_emp.dept_no
inner join
    salaries on dept_emp.emp_no = salaries.emp_no
    and subquery.max_salary = salaries.salary
order by
    dept_emp.dept_no

发表于 2024-11-01 19:41:59 回复(0)
Mysql
想提桶的奇亚籽在迎接offer头像 想提桶的奇亚籽在迎接offer
select t1.dept_no ,t1.emp_no,t1.salary from (select d.dept_no,d.emp_no,
salary,rank()over(partition by dept_no order by salary desc) r FROM salaries AS s INNER JOIN dept_emp As d ON d.emp_no = s.emp_no) t1 where t1.r = 1 order by t1.dept_no
发表于 2024-11-01 17:56:16 回复(0)
Mysql
牛客338711080号头像 牛客338711080号
select T1.dept_no,T1.emp_no,T1.salary from (
SELECT *,rank() over (partition by T.dept_no order by T.salary DESC) as rn
from
(select D.dept_no,D.emp_no,S.salary from dept_emp D left join salaries S  ON D.emp_no =S.emp_no) T) T1
WHERE T1.rn=1
;
发表于 2024-11-01 16:24:22 回复(0)
Mysql
瓷瓷瓷头像 瓷瓷瓷
老师们，这样运行出来d002有两个最高薪资的人应该怎么去重啊，，
select
    a.dept_no,
    b.emp_no,
    a.salary
from (
        select
    d.dept_no,
    max(salary) as salary
    from dept_emp d
    left join salaries s using(emp_no)
    group by dept_no
) as a
left join (
    select
        d.dept_no,
        d.emp_no,
        s.salary
    from dept_emp d
    join salaries s using(emp_no)
) as b
using(salary)
order by dept_no

发表于 2024-10-28 19:12:56 回复(0)
Mysql
牛客616866490号头像 牛客616866490号 
方法1：窗口函数
select a.dept_no,a.emp_no,a.salary from (
select d.dept_no,d.emp_no, s.salary,
rank() over(partition by d.dept_no order by s.salary desc) r
from  dept_emp  d
left  join salaries s
on d.emp_no=s.emp_no) a
where a.r=1

方法2：join，注意少匹配多，部门和薪资都要匹配上
select a.dept_no,a.emp_no,b.ms from
    (select dept_no,max(salary) ms from  dept_emp d1
    left join salaries  s1 on d1.emp_no=s1.emp_no group by dept_no) b
left  join 
    (select d.dept_no, d.emp_no,s.salary from  dept_emp d
    left join salaries  s on d.emp_no=s.emp_no) a
on a.dept_no=b.dept_no  and a.salary=b.ms
order by  a.dept_no 

发表于 2024-10-24 15:40:36 回复(0)
Mysql
牛客440842856号头像 牛客440842856号
先构造每个部门当前最高工资的表，再构造每个部门每个员工当前工资的表，两者联结，选择要输出的列再进行排序即可
select tm.d1 as dept_no,emp_no,ms as maxSalary from
(select dept_no as d1,max(salary) as ms from dept_emp as de
inner join salaries as s
on de.emp_no=s.emp_no and s.to_date='9999-01-01' and de.to_date='9999-01-01'
group by dept_no) as tm
inner join
(select dept_no as d2,de.emp_no,salary from dept_emp as de
inner join salaries as s
on de.emp_no=s.emp_no and s.to_date='9999-01-01' and de.to_date='9999-01-01') as t0
on tm.d1=t0.d2 and tm.ms=t0.salary
order by tm.d1;
发表于 2024-10-16 17:40:08 回复(0)
Mysql
勤奋努力的查理一定要上岸头像 勤奋努力的查理一定要上岸
1.直接按照部门分组group by，不就能得出部门里最高薪资的那个人了吗？
    哈哈哈哈，不行！因为使用了group by，前面的查询字段要么在group by后面出现，要么是聚合函数！！
    可是如果把员工id也加上，那不就相当于使用（部门id，员工id）组合来分组嘛，这分了就相当于没分。
2.因此，先不着急查询员工id。可以先想办法把每个部门里的最大薪资统计出来，仅仅使用部门id分组，统计每个部门下的最大薪资，就像（）里的查询一样，这样的话部门id就可以和最大薪资对应起来啦。把结果作为一个temp（部门id，部门薪水最大值）。
3.有了temp就好办多了，只要按照原来同样的思路写一个左连接查询，再左接上我们的temp，就可以仅仅保留下来每个部门中薪资水平等于最大值的数据了。 
select dp.dept_no dep, sa.emp_no emp, sa.salary salary
from dept_emp dp
    left join salaries sa
    on dp.emp_no = sa.emp_no
    left join (select dp.dept_no tempdeptno, max(salary) max_salary
                    from dept_emp dp
                    left join salaries sa
                    on dp.emp_no = sa.emp_no
                    group by dp.dept_no) as temp
    on dp.dept_no = temp.tempdeptno
    where sa.salary = temp.max_salary
order by dp.dept_no


发表于 2024-09-27 13:27:08 回复(0)
Mysql
人间四月天__头像 人间四月天__ 
select distinct a.dept_no,b.emp_no,a.salary maxSalary
from  (select dept_no,max(salary) salary from dept_emp e join salaries s on e.emp_no=s.emp_no group by dept_no) a 
 join (select s.emp_no,dept_emp.dept_no,salary from dept_emp join salaries s on dept_emp.emp_no=s.emp_no) b
 on a.salary=b.salary and a.dept_no=b.dept_no
order by a.dept_no

发表于 2024-09-23 23:17:14 回复(0)
Mysql
小白求职__头像 小白求职__

在 SQL 查询中，如果一个列既没有出现在GROUP BY子句中，也没有使用聚合函数，那么会导致错误。SQL 要求在使用GROUP BY时，所有未参与分组的列必须通过聚合函数来处理（如SUM,COUNT,AVG,MAX,MIN等），否则无法确定如何处理这些列的多个值。

如果遇到这种情况，你可以采用以下几种策略：

1. 确定聚合函数

将不出现在GROUP BY中的列使用聚合函数处理。常见的选择是MAX或MIN，以便从该列的多条记录中选出一个值。

例如，如果你想要查询每个部门的最高工资并显示员工编号，你可以这样做： 


	SELECT dept_no, MAX(salary), MAX(emp_no) FROM salaries GROUP BY dept_no;

在这个例子中，我们用MAX(emp_no)解决了emp_no无法直接出现在GROUP BY中的问题。

2. 使用窗口函数（如适用的数据库）

如果你需要在分组的同时保留某个列的值，并且你的数据库支持窗口函数（如 MySQL 8.0+、PostgreSQL 等），可以使用窗口函数来实现。例如，下面是一个常用的例子，使用ROW_NUMBER()来找到每个分组中的特定值： 


	SELECT emp_no, salary FROM ( SELECT emp_no, salary, ROW_NUMBER() OVER (PARTITION BY dept_no ORDER BY salary DESC) as rank FROM salaries
) ranked_salaries WHERE rank = 1;

这个例子按部门dept_no对工资进行分组，并且选出每个部门工资最高的员工。

3. 子查询

如果你想在GROUP BY中显示未分组的列，另一种策略是使用子查询，在子查询中先进行分组，然后在外部查询中选择其他列。例如： 


	SELECT emp_no, salary FROM salaries s WHERE salary = (SELECT MAX(salary) FROM salaries WHERE s.dept_no = dept_no);

这个例子可以找到每个部门中工资最高的员工和他们的工资。

总结

为了避免违反 SQL 规则，如果列没有出现在GROUP BY中，你可以：

  1. 对该列使用聚合函数（如MAX、MIN）。
  2. 使用窗口函数来处理分组。
  3. 使用子查询或其他 SQL 结构进行处理。
发表于 2024-09-15 14:29:08 回复(0)
Mysql
在开会的大熊猫很英俊头像 在开会的大熊猫很英俊
提供一种使用窗口函数的写法：
select dept_no,emp_no,salary as maxSalary from (
select dept_no,emp_no,salary,sum(r) over (partition by dept_no order by salary desc ) as rankkk
from
(
select a.dept_no,a.emp_no,b.salary ,1 as r
from
dept_emp a
left join
salaries b
on a.emp_no=b.emp_no) c) d
where rankkk=1

发表于 2024-09-14 15:24:49 回复(0)
Mysql
lynn_z头像 lynn_z 
select de.dept_no,de.emp_no,s.salary as maxSalary
from dept_emp de
inner join salaries s on de.emp_no=s.emp_no
where (de.dept_no,s.salary) in (
    select de.dept_no,max(s.salary) as salary
    from dept_emp de
    inner join salaries s on de.emp_no=s.emp_no
    group by de.dept_no
)
order by de.dept_no asc
1.子查询找到每个部门最高的薪资
2.连接+where in找到部门、最高薪资对应记录，从而得到薪资最高的员工编号
发表于 2024-09-02 17:38:08 回复(0)
Mysql
二三学SQL头像 二三学SQL
select ms.dept_no,ms.emp_no,ms.salary as maxSalary
from (
select dept_no,d.emp_no,salary,row_number() over(partition by dept_no order by salary desc) as maxSalary
from dept_emp d inner join salaries s on d.emp_no=s.emp_no
) ms
where maxSalary=1
order by dept_no

发表于 2024-08-26 18:02:38 回复(0)
Mysql
Renaissancee头像 Renaissancee 
select
    d.dept_no,
    d.emp_no,
    salary
from
    dept_emp d
    left join salaries s on d.emp_no = s.emp_no
where
    (dept_no, salary) in (
        select
            dept_no,
            max(salary)
        from
            dept_emp d
            left join salaries s on d.emp_no = s.emp_no
        group by
            d.dept_no
    )
order by
    d.dept_no ascz
这道题加深了对group by的使用，group by 查到是一组信息，组内的非唯一值是无法直接查出来的

发表于 2024-08-22 14:40:14 回复(0)
Mysql
牛客616907684号头像 牛客616907684号
SELECT j.dept_no,j.emp_no,j.salary as maxSalary FROM (SELECT a.emp_no,a.dept_no,b.salary  FROM dept_emp as a LEFT JOIN salaries as b on a.emp_no = b.emp_no) as j LEFT JOIN 
(SELECT a.dept_no,MAX(b.salary) as sal FROM dept_emp as a LEFT JOIN salaries as b on a.emp_no = b.emp_no GROUP BY a.dept_no) as i
on j.dept_no = i.dept_no WHERE j.salary = i.sal order by j.dept_no
发表于 2024-08-15 17:03:03 回复(0)
Mysql
对击斗魂头像 对击斗魂
这样写也通过了：
select new.dept_no,new.emp_no,new.salary maxsalary
from (select d.dept_no,d.emp_no,s.salary
,rank() over(partition by d.dept_no order by s.salary desc) rk
from dept_emp d join 
salaries s
on d.emp_no = s.emp_no) new
where rk = 1;


发表于 2024-08-13 19:24:35 回复(0)
Mysql
西红柿炒番茄s头像 西红柿炒番茄s 
SELECT 
    p.dept_no,p.emp_no,p.salary AS maxSalary
FROM
(SELECT 
    d1.dept_no,s1.emp_no,s1.salary,
    RANK() OVER(PARTITION BY d1.dept_no ORDER BY  s1.salary DESC) AS r
FROM dept_emp AS d1 , salaries AS s1
WHERE 
d1.emp_no = s1.emp_no 
AND
NOW() BETWEEN s1.from_date AND s1.to_date
) AS p 
WHERE p.r=1
ORDER BY p.dept_no

发表于 2024-08-02 23:05:06 回复(0)
Mysql
准备进厂的马后炮炮手很会编程头像 准备进厂的马后炮炮手很会编程 
#筛选出所需要的值，同时join  salaies表 关联条件为(关键点！！) s.emp_om=tp1.emp_no and s.salary = tp1.maxSalary

SELECT
    dept_no,
    tp1.emp_no,
    maxSalary
FROM
     #开窗函数按照dept_no分组并求出最大值maxSalary，注意要进行两个表join      
            (SELECT
         dept_no,
         d.emp_no,
         MAX(salary) OVER (PARTITION BY dept_no) AS maxSalary
     FROM
         dept_emp d
             JOIN salaries s ON d.emp_no = s.emp_no) AS tp1
        JOIN salaries s ON s.emp_no = tp1.emp_no AND s.salary = tp1.maxSalary
#对dept_on 进行排序
ORDER BY
    dept_no;

发表于 2024-07-26 14:34:36 回复(0)
Mysql
刷牛客的孤勇者很野蛮头像 刷牛客的孤勇者很野蛮 
select 
    dept_no
    ,t1.emp_no
    ,salary maxsalary
from dept_emp t1
    join salaries t2 on t1.emp_no=t2.emp_no
where (dept_no,salary) in (
    select 
        dept_no
        ,max(salary) 
    from dept_emp t1 
        join salaries t2 on t1.emp_no=t2.emp_no
    where t1.to_date='9999-01-01' 
        and t2.to_date='9999-01-01'
    group by dept_no
)
order by dept_no

发表于 2024-07-18 16:09:58 回复(0)
提交观点

问题信息

难度：
552条回答 64579浏览

热门推荐

通过挑战的用户

查看代码

相关试题

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

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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