博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
184. Department Highest Salary (medium)
阅读量:5150 次
发布时间:2019-06-13

本文共 1417 字,大约阅读时间需要 4 分钟。

Source: 

Description:

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+| Id | Name | Salary | DepartmentId |+----+-------+--------+--------------+| 1 | Joe | 70000 | 1 || 2 | Henry | 80000 | 2 || 3 | Sam | 60000 | 2 || 4 | Max | 90000 | 1 |+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+| Id | Name |+----+----------+| 1 | IT || 2 | Sales |+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+| Department | Employee | Salary |+------------+----------+--------+| IT | Max | 90000 || Sales | Henry | 80000 |+------------+----------+--------+

 

Solution:

select  distinct t2.name as department, t1.employee, t1.salary from(    SELECT     e1.name as employee, e1.salary as salary,e1.departmentid  from     (selEct * from Employee ) e1    inner JOIN    (select departmentid,max(salary) as salary from Employee  group by departmentid) e2    on e1.departmentid = e2.departmentid and     e1.salary = e2.salary)t1inner JOIN(select * from Department) t2on t1.departmentid = t2.id

 

转载于:https://www.cnblogs.com/sixu/p/6884898.html

你可能感兴趣的文章
C语言 链队列基本操作
查看>>
OO学习总结与体会
查看>>
虚拟机长时间不关造成的问题
查看>>
toString和valueOf的区别
查看>>
C#操作Excel(创建、打开、读写、保存)几种方法的总结
查看>>
校门外的树2 contest 树状数组练习 T4
查看>>
JS及JQ使用JSONP实现跨域调用必应搜索
查看>>
面试整理:Python基础
查看>>
Python核心编程——多线程threading和队列
查看>>
三次数模总结一下
查看>>
Py之np.concatenate函数【转载】
查看>>
洛谷 1571 眼红的Medusa
查看>>
spring--百度百科
查看>>
关于Invoke和InvokeRequired
查看>>
Program exited with code **** 相关解释
查看>>
装服务器,测试数据库,简单的maven命令
查看>>
升级Firefox8后watir-webdriver出现错误“unable to obtain stable firefox connection in 60 seconds”...
查看>>
第6章 Overlapped I/O, 在你身后变戏法 ---被激发的 Event 对象 -4
查看>>
植物大战僵尸中文年度版
查看>>
26、linux 几个C函数,nanosleep,lstat,unlink
查看>>