【sql学习】LeetCode之176. Second Highest Salary

Write a SQL query to get the second highest salary from the Employee table.

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

For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

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

别人的代码：

方法一

Runtime: 126 ms, faster than 82.15% of MySQL online submissions for Second Highest Salary.

Memory Usage: N/A

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

查询出没有重复值的第二名，即假如最高的身高是10000，有几个人同时工资是10000，则查出工资小于10000的所有工资中的最高工资（也就是全体的第二大）　　　　

意义：先查询出最高的值，然后查询所有小于该值中的最高值。

Using max() will return a NULL if the value doesn't exist. So there is no need to UNION a NULL. Of course, if the second highest value is guaranteed to exist, using LIMIT 1,1 will be the best answer.

 

方式二：

SELECT
	Salary SecondHighestSalary 
FROM
	Employee
GROUP BY
	Salary
ORDER BY
	Salary DESC
LIMIT 1 OFFSET 1

这种方式就是对于上面这种整张表只有一条记录的情况，通不过；

主要知识点： 
         a）limit的使用方法，limit n返回前n条记录，limit n offest m从m+1条记录开始返回n条记录 。

         b）代码中使用group by Salary的作用是去重，对于大数据查询时使用group by去重比distinct的效率要高一些。

         c）LIMIT 1 OFFSET 1等价于写：limit 1,1

         d）LIMIT 1 OFFSET n-1，便是求出第n大数

 

Runtime: 142 ms, faster than 36.24% of MySQL online submissions for Second Highest Salary.

Memory Usage: N/A

select 
(select distinct salary
from Employee
order by salary DESC
limit 1 offset 1)
 as SecondHighestSalary;

          b）这里便是使用的distinct来进行去重；

ps:

why 
(1) can handle null properly;
select (select distinct salary
from Employee
order by salary DESC
limit 1 offset 1) as SecondHighestSalary;

but (2) can not;
select distinct salary as SecondHighestSalary
from Employee
order by salary DESC
limit 1 offset 1;

interesting question, share my understanding after reading some posts. Query #2 (the inner layer of #1) returns the value as empty string "" (it is still a value though empty).
NULL, on the other hand is the absence of value or undefined. This happens when the outer layer select from an empty table throws NULL

简单理解就是：

（2）返回的是一个空集，但是空集不是NULL，

（1）如果对一个空集，进行select，就会返回NULL