SQL——leetcode练习_the employee table holds all employees. every empl-优快云博客

本文提供MySQL查询案例，包括如何找出员工表中第二高的薪资，以及各部门薪资最高的员工。通过子查询和连接操作，展示SQL解决复杂问题的能力。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Second 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 your MySQL query statement below

select max(Salary) as SecondHighestSalary from Employee
where Salary not in (select max(Salary) from Employee)

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  |
+------------+----------+--------+

Write your MySQL query statement below

select 
    t.Name as  Department,
    e.Name as Employee,
    t.Salary from(
    select 
        e.DepartmentId,
        d.name as Name,
        max(e.Salary) as Salary from Employee e
    join Department d on e.DepartmentId=d.id
    group by  DepartmentId) as t
join Employee e 
on t.DepartmentId=e.DepartmentId and e.Salary=t.Salary