（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 0,1;
SELECT * FROM Employee LIMIT 1 OFFSET 0;
SELECT * FROM products LIMIT 1;
“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的结果
参考链接
发表评论