mysql 第二高薪水

时间:2022-06-14 21:52:28

(1)第二高薪水

编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary)

+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+

例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+

刚一看题目,觉得很简单的,可是做了一下之后才发现很多细节都没考虑,特此记录下来

select (select distinct Salary  from Employee order by Salary desc limit 1,1)as SecondHighestSalary

select (select distinct Salary  from Employee order by Salary desc limit 1 offset 1)as SecondHighestSalary

limit限制结果范围

SELECT * FROM Employee LIMIT ,;
SELECT * FROM Employee LIMIT OFFSET ;
SELECT * FROM products LIMIT ;

“0”: 代表数据获取的起始位置.(0代表第一条记录,以此递增) 
“1”: 期望获取的记录条数.

当获取的记录是从第一条开始则可以省略书写起始位置 “0”

(2)部门工资最高的员工

Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。

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

Department 表包含公司所有部门的信息。

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

编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
SELECT d. NAME AS Department,e. NAME AS Employee,e.Salary FROM Department d,Employee e
WHERE d.Id = e.DepartmentId AND (e.Salary, e.DepartmentId) IN (SELECT Max(Salary),DepartmentId FROM Employee GROUP BY DepartmentId)

(3)部门工资前三高的员工

Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id 。

+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

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

编写一个 SQL 查询,找出每个部门工资前三高的员工。例如,根据上述给定的表格,查询结果应返回:

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
select d.Name as Department,e.Name Employee,e.Salary from Employee e,Department d
where
e.DepartmentId=d.Id and
Salary>=IFNULL((select distinct e1.Salary from Employee as e1 where e.DepartmentId=e1.DepartmentId order by e1.Salary desc limit 2,1),0)
order by DepartmentId,Salary desc;

说明:

  IFNULL(expression_1,expression_2);

  如果expression_1不为NULL,则IFNULL函数返回expression_1; 否则返回expression_2的结果