# int32bit / leetcode

Fetching latest commit…
Cannot retrieve the latest commit at this time.
Type Name Latest commit message Commit time
..
Failed to load latest commit information.
solve.sql

# Department Highest Salary

## Problem

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 1

```select d.Name as Department, e.Name as Employee, max(e.Salary) as Salary
from Employee as e
join Department as d on e.DepartmentId = d.Id
group by e.DepartmentId;```

``````mysql> select DepartmentId, max(Salary) as max from Employee group by DepartmentId;
+--------------+-------+
| DepartmentId | max   |
+--------------+-------+
|            1 | 90000 |
|            2 | 80000 |
+--------------+-------+
``````

## Code

```select d.Name as Department, e1.Name as Employee, e1.Salary as Salary
from Employee as e1
join (select DepartmentId, max(Salary) as max from Employee group by DepartmentId) as t
on e1.DepartmentId = t.DepartmentId
join Department as d
on e1.DepartmentId = d.Id
where e1.Salary = max;```

## Code

```SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary
from
Employee E,
Department D
WHERE E.DepartmentId = D.id
AND (DepartmentId,Salary) in
(SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId) ```

## 关于聚集查询

``````	COUNT ([ALL | DISTINCT] *)

<聚集函数> ([ALL | DISTINCT] <值表达式>)
``````

## 分组

SQL语言提供了`group by`子句，其一般形式如下:

``````group by <分组列>{,<分组列>} [having <分组条件>]
``````

You can’t perform that action at this time.