首页 > 试题广场 >

查询每个部门中工资最高的员工 有两张表，一张表为

[问答题]
查询每个部门中工资最高的员工

有两张表，一张表为员工表，包含员工ID，员工姓名，员工工资和员工所在的部门ID，


另一张表为部门表，包含部门ID和部门名称。

写出查询每个部门中工资最高的员工信息的SQL。对于上述两张表，输出应如下：



查看答案及解析
添加回答
牛客893872645号头像 牛客893872645号
SELECT Department.name as "Department", Employee.name as "Employee", Salary 
FROM Employee 
JOIN Department 
ON Employee.departmentId = Department.Id 
WHERE (Employee.departmentId, Salary) IN ( SELECT departmentId, MAX(Salary) 
FROM Employee GROUP BY departmentId ) ;
发表于 2020-11-04 21:58:18 回复(0)
金州小铁匠头像 金州小铁匠
# window function
select d.Name as Department, e.Name as Employee, Highest as Salary from (
select Name,Salary,DepartmentId, MAX() over(partition by DepartmentId order by Salary desc) as Highest
from Employee 

) e
join Department d 
on d.Id = e.DepartmentId

# no window function
select e.Name as Employee,d.Name as Department, highest as Salary from Employee e
join
(select max(salary) as highest,DepartmentId from Employee
group by 2) high
on high.highest = e.Salary and high.DepartmentId = e.DepartmentID
join Department d
on d.Id = e.DepartmentId


发表于 2020-06-05 04:48:27 回复(0)
星晴(≧ω≦)/头像 星晴(≧ω≦)/
select d.Name as Department, e.Name as Employee, e.Salary from Department d inner join Employee e on d.Id = e.DepartmentId and e.Salary >= (select max(Salary) from Employee where DepartmentId = d.Id)
发表于 2019-04-20 11:29:04 回复(0)
单单单单单单头像 单单单单单单
法1：
select 部门表.name as departmen , new2.name as employee , new2.salary
from 部门表
join 
(
select * from
  (
   select name,salary,departmentid,row_number () over (partition by departmentid order by salary desc) as od
   from 员工表
   )  as new1 where od<2
) as new2
on 部门表.id = new2.departmentid

法2：
select 部门表.name as departmen , new2.name as employee , new2.salary
from 部门表
join 
(
   select name,salary,departmentid,row_number () over (partition by departmentid order by salary desc) as od
   from 员工表 
) as new2
on 部门表.id = new2.departmentid
where new2.od < 2


发表于 2019-04-08 11:21:09 回复(0)
提交观点

问题信息

算法工程师 2019 大数据开发工程师 招商银行信用卡中心
上传者：小小
难度：
4条回答 1921浏览

热门推荐

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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