[LeetCode]-DataBase-Department Highest Salary
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 |
+------------+----------+--------+ 需求:查询每个部门工资最高的员工
CREATE TABLE Employee(
Id TINYINT UNSIGNED,
Name VARCHAR(20),
Salary DECIMAL(10,2),
DepartmentId TINYINT
)ENGINE=MyISAM CHARSET=utf8;
CREATE TABLE Department(
Id TINYINT UNSIGNED,
Name VARCHAR(20)
)ENGINE=MyISAM CHARSET=utf8;
SELECT b.nm,a.Name,a.salary
FROM employee a INNER JOIN (
SELECT t2.Id,t2.Name nm,MAX(t1.salary) sal
FROM employee t1 INNER JOIN department t2 ON t1.DepartmentId=t2.Id
GROUP BY t1.DepartmentId
)b ON a.salary=b.sal AND a.DepartmentId=b.Id
最新文章
- 如何在文章/随笔中添加可运行的js代码
- 【Python⑤】python序列---list和tuple
- Python不完全入门指南
- Unity3D骨骼动画的分解(CleanData.Ani详解)
- C读取文件
- SharePoint安全 - SharePoint网站常用页面URL索引
- Kerberos认证流程详解
- 编码实战Web端联系人的增删改查
- vim 多窗口编辑
- spring学习——注入静态对象属性
- 关于tween.js测试介绍
- kettle文件自动化部署(shell脚本执行):命令行参数传入
- .Net Core实践4 web 反向代理
- Github使用: 本地上传, 与之同步
- Android内存优化(一)Dalvik虚拟机和ART虚拟机对比
- Coursera, Deep Learning 1, Neural Networks and Deep Learning - week4, Deep Neural Networks
- 最新的Delphi版本号对照
- 【C/C++】泛型栈
- ==和equal()的区别
- php OPcache