mysql查询每个部门工资最高的员工信息_获取每个部门中当前员工薪水最高的相关信息...

最新推荐文章于 2023-11-19 13:38:56 发布

原创最新推荐文章于 2023-11-19 13:38:56 发布 · 7.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql查询每个部门工资最高的员工信息

这篇博客探讨了在MySQL中查询每个部门工资最高的员工信息时常见的错误和解决方案。博主指出，由于`GROUP BY`语句的特性，直接使用`MAX(salary)`可能无法正确匹配员工编号。提供了多种正确解答，强调了在查询中需要确保工资最大值与员工编号对应，并按部门编号升序排列。解答包括子查询、窗口函数以及使用`DENSE_RANK()`等方法确保正确性。

目前的一些“正确答案”其实不对，因为emp_no是随机返回的，而不是与最大工资相对应的那个

【知识点】

使用group by子句时，select子句中只能有聚合键、聚合函数、常数。

emp_no并不符合这个要求。

楼上wasrehpic与Linkkuma的答案都可以返回正确值，此处再提供一个参考答案

select de.dept_no, de.emp_no, s.salary

from dept_emp de inner join salaries s

on de.emp_no = s.emp_no

and de.to_date = '9999-01-01'

and s.to_date = '9999-01-01'

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

from dept_emp de2 inner join salaries s2

on de2.emp_no = s2.emp_no

and de2.to_date = '9999-01-01'

and s2.to_date = '9999-01-01'

where de2.dept_no = de.dept_no

group by de2.dept_no)

order by de.dept_no

最后一行的order by子句一定不能少，因为原题目可能漏了一个条件，“按照部门升序排列”

(真是万万没想到。。。)

发表于 2018-08-04 13:18:37

有的高票的都是错的，MAX(SALARY) 和 emp_no 不一定对应哦！！！

GROUP BY 默认取非聚合的第一条记录！！！！！！

编辑于 2020-11-07 15:04:45

161

此题思路如下：

1、先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no；

2、选取每个员工当前的工资水平，用d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'作条件限制，因为此表中每条最新记录的 to_date 都用 9999-01-01 表示；

3、用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者；

4、将salaries用s代替，dept_emp用d代替，最后将MAX(s.salary)用salary代替后输出。

SELECT d.dept_no, s.emp_no, MAX(s.salary) AS 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

------------------------------------------分割线：若存在多条最大记录----------------------------------------

有同学提出疑问，如果存在多条最大记录怎么办？而 MAX 函数根据不同数据库只选择最前一条或最后一条最大记录，其余记录均被忽略。此时解法如下：

1、创建两张表，一张为maxsalary，用于存放当前每个部门薪水的最大值；另一张为currentsalary，用于存放当前每个部门所有员工的编号和薪水；

2、限定条件为两张表的 dept_no 和 salary 相等，这样就可以找出当前每个部门所有薪水等于最大值的员工的相关信息了；

3、最后记得根据 currentsalary.dept_no 升序排列，输出与参考答案相同的记录表。

4、以下代码虽然很长，仔细一看都是基于上面的基础解法变化而来的，中心思想就是绕开 MAX 的特性限制，运用比较的方法选出多个相同的最大值。 SELECT currentsalary.dept_no, currentsalary.emp_no, currentsalary.salary AS salary

FROM

//创建maxsalary表用于存放当前每个部门薪水的最大值

(SELECT d.dept_no, MAX(s.salary) AS 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) AS maxsalary,

//创建currentsalary表用于存放当前每个部门所有员工的编号和薪水

(SELECT d.dept_no, s.emp_no, s.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'

) AS currentsalary

//限定条件为两表的dept_no和salary均相等

WHERE currentsalary.dept_no = maxsalary.dept_no

AND currentsalary.salary = maxsalary.salary

//最后以currentsalary.dept_no排序输出符合要求的记录表

ORDER BY currentsalary.dept_no

编辑于 2017-09-13 16:29:27

翻了前几条回复最多的解法，没看到我认为完美的0漏洞解法。编辑器可能很多写法都能通过，但是通过不代表没问题。

此题常见漏洞：1.emp_no直接和group by dept_no一起使用，拿到了最大salary但是存在emp_no取值其实与salary不匹配的问题；2. 先使用group by获得最高salary，再去用最高salary匹配两表返回dept_no,emp_no信息，这存在A部门的最高薪水，等于B部门非最高薪水时，B部门的非最高薪水也会被显示出来。在此我将我的答案提供出来，自认为不存在以上两点漏洞：

解法一：(如果同部门有多条同等最大salary，一起显示出来)

select r.dept_no,ss.emp_no,r.maxSalary from (

select d.dept_no,max(s.salary)as maxSalary from dept_emp d,salaries s

where d.emp_no=s.emp_no

and d.to_date='9999-01-01'

and s.to_date='9999-01-01'

group by d.dept_no

)as r,salaries ss,dept_emp dd

where r.maxSalary=ss.salary

and r.dept_no=dd.dept_no

and dd.emp_no=ss.emp_no

and ss.to_date='9999-01-01'

and dd.to_date='9999-01-01'

order by r.dept_no asc 解法二：(如果同部门有多条同等最大salary，仅显示一条)

select r.dept_no,r.emp_no,max(r.salary) from (

select d.dept_no,d.emp_no,s.salary from dept_emp d,salaries s

where d.emp_no=s.emp_no

and d.to_date='9999-01-01'

and s.to_date='9999-01-01'

order by s.salary desc

)as r

group by r.dept_no

order by r.dept_no asc 注明两点：

1.题目忘记写一条信息，按照部门编号排序

2.解法二利用了GROUP BY 默认取非聚合数据的第一条记录，所以先排好序，拿到的emp_no第一条信息，也是与最大salary匹配的

3.解法一中使用多表取值，where筛选条件和内连接，on筛选条件，效果一致，可以替换。效率根据不同表的结构，数据结构而定。

发表于 2019-09-12 18:18:04

本题是一道审核有问题的题目，分为2种情况(用的提交错误时提供的数据)：

第一种方案：

SELECT B.dept_no,B.emp_no,A.salary AS salary FROM `salaries` AS A join dept_emp AS B on A.emp_no=B.emp_no

where A.to_date='9999-01-01' and B.to_date='9999-01-01'

GROUP BY B.dept_no

having A.salary=max(A.salary);

说明：用这种方案，先分组，你会发现它分组后默认都是取的第一条数据：

然后我后一句having A.salary=max(A.salary);就会把10003给干掉了，就是查不出来了：

原因是10003与10004同属部门d004，而10004的薪水高于10003，所以max(A.salary)不等于A.salary，导致没有了这条记录。这是此方案存在的问题，把应该显示的10004号员工，对应d004部门的记录给弄丢了，但此方案竟然神奇的通过了。

第二种方案：

SELECT B.dept_no,A.emp_no,MAX(A.salary) AS salary FROM `salaries` AS A join dept_emp AS B on A.emp_no=B.emp_no

where A.to_date='9999-01-01' and B.to_date='9999-01-01'

GROUP BY B.dept_no;

它的结果是：

我们注意d004部门那条记录，是10003号员工对应薪水74057，看表我们知道，10003号员工对应的薪水是43311,10004号员工对应的薪水是74057，这里的结果错位了，错误的原因是因为我用了select MAX(A.salary)，查出来的薪水确实的每组最高的，但是它emp_no却默认取了分组后的第一条记录的emp_no。这个方案居然也通过了，我真是纳闷了，大家都没发现吗？？？？如果我说错了的话希望有大神指教，欢迎讨论啊~不要沉！！！！

找了个好办法，却有问题

SELECT dept_no,emp_no,salary from(

SELECT D.dept_no AS dept_no,S.emp_no AS emp_no,S.salary AS salary from salaries AS S JOIN dept_emp as D on S.emp_no=D.emp_no

where D.to_date='9999-01-01' and S.to_date='9999-01-01'

ORDER BY salary DESC

) as b

GROUP BY dept_no

先进行降序排序，再分组，结果却是

仍然显示的是10003的43311，绝望啊，我尝试了下，如果先去掉分组：

同样是d001部门，分组后10001怎么就在10002前面，而d004部门的10003怎么跑10004前面去了，这个题有毒啊

编辑于 2018-01-04 17:09:59

--评论区的一些答案没有把GROUP BY 默认取非聚合的第一条记录考虑进去

--以下是我的答案：

SELECT r1.dept_no, r1.emp_no, r1.salary

FROM

--创建r1表用于存放当前每个部门每个员工的薪水

(

SELECT d.dept_no, d.emp_no, s1.salary

FROM dept_emp d, salaries s1

WHERE d.to_date='9999-01-01'

AND s1.to_date='9999-01-01'

AND d.emp_no = s1.emp_no)r1

JOIN --创建r2表用于存放当前每个部门薪水的最大值

(

SELECT d.dept_no, MAX(s2.salary) as maxsalary

FROM dept_emp d

--为了避免GROUP BY默认取非聚合数据的第一条记录，先把salary排好序

JOIN (SELECT * FROM salaries ORDER BY salary DESC)s2

ON d.emp_no = s2.emp_no

WHERE d.to_date='9999-01-01'

AND s2.to_date='9999-01-01'

GROUP BY d.dept_no)r2

ON r1.salary = r2.maxsalary

AND r1.dept_no = r2.dept_no

ORDER BY r2.dept_no

编辑于 2019-10-01 22:50:22

1、方法一：使用窗口排序函数

SELECT D.dept_no,D.emp_no,D.salary

FROM(

SELECT

DENSE_RANK() OVER (PARTITION BY C.dept_no ORDER BY C.salary DESC) AS raking,

C.dept_no,

C.emp_no,

C.salary

FROM (

SELECT

A.dept_no,

A.emp_no,

B.salary

FROM

dept_emp A

INNER JOIN salaries B ON A.emp_no = B.emp_no

WHERE

A.to_date = '9999-01-01'

AND B.to_date = '9999-01-01'

) C

) D

WHERE D.raking = 1

ORDER BY D.dept_no 【注】DENSE_RANK() OVER(PARTITION dept_no ORDER BY salary DESC)可以得出以

部门为单位的员工的工资排名，可以满足并列第1的要求

2、方法二：如果mysql数据库没有排序函数，则可以使用非等值自连接的方法来实现类似DENSE_RANK()

函数的功能，语句比较长，可以不过原理简单的，如下：

SELECT D.dept_no,D.emp_no,D.salary FROM(

SELECT (

SELECT COUNT( DISTINCT F.salary )

FROM(

SELECT

A.dept_no,

A.emp_no,

B.salary

FROM

dept_emp A

INNER JOIN salaries B ON A.emp_no = B.emp_no

WHERE

A.to_date = '9999-01-01'

AND B.to_date = '9999-01-01'

) AS F

WHERE

F.salary >= C.salary

AND F.dept_no = C.dept_no

) AS raking,

C.dept_no,C.emp_no,C.salary FROM (

SELECT

A.dept_no,

A.emp_no,

B.salary

FROM

dept_emp A

INNER JOIN salaries B ON A.emp_no = B.emp_no

WHERE

A.to_date = '9999-01-01'

AND B.to_date = '9999-01-01' ) C ) D

WHERE D.raking = 1

ORDER BY D.dept_no

编辑于 2019-07-02 23:46:36

这道题目的判题系统有问题, 很多人都回答错误了, 但是通过了判题系统

下面是真`正确答案的写法:

写法一: select dept_emp.dept_no as dept_no_a, dept_emp.emp_no, max(salaries.salary) as salary

from dept_emp,salaries

where salaries.emp_no=dept_emp.emp_no

group by dept_emp.emp_no,dept_emp.dept_no

having max(salaries.salary) =

(

select max(salaries.salary)

from dept_emp inner join salaries

on salaries.emp_no=dept_emp.emp_no

where dept_emp.dept_no = dept_no_a

)

order by dept_no_a

写法二: select distinct dept_no, s.emp_no, salary

from dept_emp as d inner join salaries as s

on s.emp_no = d.emp_no and s.salary =

(select s2.salary

from salaries as s2 inner join dept_emp as d2

on s2.emp_no = d2.emp_no

where d2.dept_no = d.dept_no

order by s2.salary desc

limit 1

)

order by dept_no

;

写法三:

受限于any_value的实现,如果是返回第一个值就没有问题 select ret.dept_no, any_value(ret.emp_no), max(ret.salary)

from

(select dept_emp.dept_no,dept_emp.emp_no, max(salaries.salary) as salary

from dept_emp,salaries

where salaries.emp_no=dept_emp.emp_no

group by dept_emp.emp_no,dept_emp.dept_no order by salary desc

) as ret group by ret.dept_no

发表于 2018-04-25 16:21:55

个人认为这个是最严谨最正确的写法

1.按照emp_no连接两个表

2.注意时间需要是当前两边表的date都要是99990101

3.group by部门号dept_no 添加条件工资=最高工资 SELECT e.dept_no, e.emp_no, s.salary

FROM dept_emp AS e INNER JOIN salaries AS s

ON e.emp_no = s.emp_no

WHERE e.to_date = '9999-01-01' AND s.to_date = '9999-01-01'

GROUP BY e.dept_no

HAVING s.salary = MAX(s.salary);

编辑于 2019-03-18 10:26:45

高分答案和很多通过的情况其实不正确，主要是聚合函数求值后对应的问题。请耐心看完下面的：

自己本地数据库模拟了下：

----------

1.(本题通过，但是d004部门的数据丢了)

SELECT

d.dept_no,

d.emp_no,

s.salary

FROM

dept_emp d,

salaries s

WHERE

d.emp_no = s.emp_no

AND d.to_date = '9999-01-01' and s.to_date='9999-01-01'

GROUP BY

d.dept_no

HAVING

s.salary = max(s.salary);

2.(本题通过,但是max(s.salary)和d.emp_no不对应，d004的最高工资是10004，而非10003)

SELECT

d.dept_no,

d.emp_no,

max(s.salary)

FROM

dept_emp d,

salaries s

WHERE

d.emp_no = s.emp_no

AND d.to_date = '9999-01-01' and s.to_date ='9999-01-01'

GROUP BY

d.dept_no;

3.(网友答案)本题通过。对应关系也正确。

SELECT

re1.dept_no,

re2.emp_no,

re1.salary

FROM

(

SELECT

dept_em.dept_no,

max(em_sa.salary) salary

FROM

(

SELECT

emp_no,

salary

FROM

salaries

WHERE

to_date = '9999-01-01'

) em_sa

LEFT JOIN (

SELECT

emp_no,

dept_no

FROM

dept_emp

WHERE

to_date = '9999-01-01'

) dept_em ON em_sa.emp_no = dept_em.emp_no

)

GROUP BY

dept_no

) re1

JOIN (

SELECT

dept_em.dept_no,

em_sa.emp_no,

em_sa.salary salary

FROM

(

SELECT

emp_no,

salary

FROM

salaries

WHERE

to_date = '9999-01-01'

) em_sa

LEFT JOIN (

SELECT

emp_no,

dept_no

FROM

dept_emp

WHERE

to_date = '9999-01-01'

) dept_em ON em_sa.emp_no = dept_em.emp_no

)

) re2 ON re1.dept_no = re2.dept_no

AND re1.salary = re2.salary

ORDER BY

re1.dept_no;

4.(本题未通过，但在自己数据库上，测试通过。和第3种的输出一致)

select t.dept_no, de.emp_no,t.maxSalary as salary from dept_emp de,salaries sal,

(SELECT

d.dept_no,

max(s.salary) as maxSalary

FROM

dept_emp d,

salaries s

WHERE

d.emp_no = s.emp_no

GROUP BY

d.dept_no) t where de.dept_no = t.dept_no and de.emp_no = sal.emp_no and sal.salary = t.maxSalary AND de.to_date = '9999-01-01' and sal.to_date ='9999-01-01' ORDER BY de.dept_no;

发表于 2018-07-29 13:14:22

select dept_no,emp_no,salary 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,salaries s where d.emp_no=s.emp_no

and d.to_date='9999-01-01'

and s.to_date='9999-01-01')

a where rn=1

发表于 2018-08-09 10:26:39

group by 后面必须有select字段中非聚合函数的字段，所以个人觉得参考答案后应该还有emp_no

发表于 2017-07-17 16:58:25

select X.dept_no,Y.emp_no,Y.salary

from

(select d.dept_no,max(salary) max_salary

from dept_emp d,salaries s

where d.emp_no=s.emp_no and s.to_date='9999-01-01' and d.to_date='9999-01-01'

group by dept_no) as X,

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

from dept_emp d,salaries s

where d.emp_no=s.emp_no and s.to_date='9999-01-01' and d.to_date='9999-01-01') as Y

where X.dept_no=Y.dept_no and X.max_salary=Y.salary

order by X.dept_no

第一次写就通过了，这是我的思路，我认为十分清晰

发表于 2020-02-25 12:38:14

用开窗函数大家看看可以不

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 a.to_date desc,b.salary desc) as rank

from dept_emp a

join salaries b on a.emp_no=b.emp_no and a.to_date=b.to_date

where rank=1;

发表于 2020-03-10 13:45:40

用group by并没有很方便，还有好多答案是错的啊，这种题row_number不香吗

select tb2.dept_no,tb2.emp_no,tb2.salary from (

select *,row_number() over(partition by tb1.dept_no order by tb1.salary desc) as no from

(

select a.dept_no,a.emp_no,b.salary

from dept_emp as a join salaries as b

on a.emp_no=b.emp_no

and a.to_date='9999-01-01'

and b.to_date='9999-01-01'

)tb1

)tb2

where tb2.no=1

发表于 2020-02-06 14:42:40

select d.dept_no,d.emp_no,max(salary) salary

from dept_emp d

inner join salaries s

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,d.emp_no

group by 后面的字段必须是select后面除聚合函数的所有字段

不太理解为什么只用1个dept_no能跑通，两个反而不行

发表于 2018-08-02 15:59:22

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

from dept_emp as d inner join salaries as s

on d.emp_no = s.emp_no

having salary = max(salary)

where d.to_date = '9999-01-01'

and s.to_date = '9999-01-01'

group by d.dept_no

为什么过不了，但是 select d.dept_no, s.emp_no, max(s.salary) as salary

from dept_emp as d inner 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'

group by d.dept_no

过得了。

我感觉这两个是一样的，只不过第一个的having子句多余了。

发表于 2018-04-15 22:43:58

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

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'

group by d.dept_no

having max(s.salary); 被最后的“；”号给搞死。。才发现位置没加到最后一句，最后把它挪下来，通过！老是犯这种低级错误。。心累

发表于 2018-01-29 15:08:10

#先找出每个部门的最高工资，并与员工薪资表拼接

#后找出员工薪资与该部门对应的最高工资相等的员工信息

select t2.dept_no,t1.emp_no,t1.salary

from salaries t1

left join dept_emp t2 on t1.emp_no=t2.emp_no

left join (select t2.dept_no,max(t1.salary) max_sal

from salaries t1

left join dept_emp t2

on t1.emp_no=t2.emp_no

where t2.to_date='9999-01-01' and t1.to_date='9999-01-01'

group by t2.dept_no)t3 on t2.dept_no=t3.dept_no

where t1.salary =t3.max_sal

#请教大家，为什么这个能在本地跑出正确结果，而平台跑不出想要的结果？

发表于 2021-01-31 13:30:57

【结合数据表和输出结果进行审题】

#部门(时间限制)--员工当前薪水最高

#d001部门有3名员工，薪水表符合时间限制的有两条记录，分别来自2名员工，而输出结果只有一条。

#所以题目中的薪水最高是针对部门而不是某个员工

select d.dept_no

,s.emp_no

,s.salary

from salaries s,dept_emp d

where s.emp_no=d.emp_no

and d.to_date = '9999-01-01'

and s.to_date='9999-01-01'

and (d.dept_no,s.salary) in(select d.dept_no,max(s.salary)

from salaries s

join dept_emp d on s.emp_no=d.emp_no

where d.to_date = '9999-01-01'

and s.to_date='9999-01-01'

group by d.dept_no)

order by d.dept_no;

发表于 2021-01-26 17:57:30