sql数据库--查询第二高的值

一、题目

从emlpoyee数据库中查出第二高的值，如没有第二高的值返回NULL

二、数据表：employee

三、代码

1、如果employee表中的值是不重复的值不为空的值，下述代码均可：

SELECT * from employee ORDER BY salary DESC LIMIT 1,1

SELECT MAX(salary) as secondhighsalary FROM employee WHERE salary<(SELECT MAX(salary) FROM employee)

2、如果是下述这几种情况，仅可用下述的代码：

 

SELECT MAX(salary) as secondhighsalary FROM employee WHERE salary<(SELECT MAX(salary) FROM employee)

 四、如果是第三高或者其他高的：先去重后排序

SELECT DISTINCT(salary) AS thirdhighsalary 
FROM employee 
ORDER BY salary desc
LIMIT 2,1