176. Second Highest Salary

Problem: Write a SQL query to get the second highest salary from the Employee table. If there is no second highest salary, then the query should return null.

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

Note: consider all the possible situations! (1) 记录不存在 (2) 记录为NULL (3) 记录重复 

DISTINCT

select distinct job from emp; //查询结果集去重

LIMIT

1. limit startIndex,length

startIndex：表示记录查询起始位置，从0开始，与数组下标/索引（index）类似

length：表示查询的记录长度

select * from emp limit 1,3; //从第2条数据起，查询3条数据，即第2、3、4条数据

select * from emp limit 3; //默认startIndex=0，从第1条数据起，查询3条数据，即第1、2、3条数据

2. limit length offset startIndex

offset：表示偏移量，等同于startIndex

select * from emp limit 3 offset 1; //从第2条数据起，查询3条数据，即第2、3、4条数据

SELETE 

 当记录不存在时，即表中不存在该记录，查询结果集为Empty set

SELECT sal FROM emp ORDER BY sal LIMIT 14,1;
Empty set (0.00 sec)

 仅使用select一般用于显示数据或者赋值，当结果集为Empty set时，显示NULL

SELECT (SELECT sal FROM emp ORDER BY sal LIMIT 14,1) AS 15thSal;
+---------+
| 15thSal |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

当记录为NULL时 ，即表中存在该记录但没有值，查询结果集返回NULL

SELECT NULL;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

MAX() 分组函数自动忽略NULL

Solution

SELECT
    (SELECT DISTINCT Salary
     FROM Employee
     ORDER BY Salary DESC
     LIMIT 1,1) AS SecondHighestSalary;

sub-query: take this as a temp table 该条答案在情况(1)(2)(3)条件下待验证

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary<(SELECT MAX(Salary) FROM Employee);