1.现有表情况如下：
Create table If Not Exists Employee (Id int, Salary int);Truncate table Employee;insert into Employee (Id, Salary) values ('1', '100');insert into Employee (Id, Salary) values ('2', '200');insert into Employee (Id, Salary) values ('3', '300'); 
如果没有第二高的Salary将返回null
2.答案
SELECT max(Salary) AS SecondHighestSalaryFROM EmployeeWHERE Salary < (SELECT max(Salary) FROM Employee)
SELECT DISTINCT (Salary) as SecondHighestSalaryFROM EmployeeUNION SELECT NULL ORDER BY SecondHighestSalary DESC LIMIT 1, 1
SELECT IFNULL( ( SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1 ), NULL ) AS SecondHighestSalary
其中 DISTINCT 表示去重
DISTINCT ：去重union 表示联合查询，去重union all 表示联合查询，不去重，会包含重复的数据desc代表降序asc代表升序limit m,n 其中m是指记录开始的index，从0开始，表示第一条记录n是指从第m+1条开始，取n条。limit 1,1 代表取出第2条数据，共一条数据select * from Employee limit 2,2即取出第3条至第4条，2条记录如果没有2条，存在多少条即返回多少条
nullif,isnull,ifnull 的用法
IFNULL(expr1,expr2)如果expr1不为null,则ifnull()的返回值为expr1，否则为expr2;其返回值为字符串或数字mysql> select ifnull("hello","world");+-------------------------+| ifnull("hello","world") |+-------------------------+| hello |+-------------------------+1 row in set (0.00 sec)mysql> select ifnull(null,"hello");+----------------------+| ifnull(null,"hello") |+----------------------+| hello |+----------------------+1 row in set (0.01 sec)mysql> select ifnull(1/0,"world");+---------------------+| ifnull(1/0,"world") |+---------------------+| world |+---------------------+1 row in set (0.00 sec)
isnull(expr) 的用法：如expr 为null，那么isnull() 的返回值为 1，否则返回值为 0。 mysql> select isnull(null);+--------------+| isnull(null) |+--------------+| 1 |+--------------+1 row in set (0.01 sec)mysql> select isnull(1);+-----------+| isnull(1) |+-----------+| 0 |+-----------+1 row in set (0.00 sec)使用= 的null 值对比通常是错误的。
NULLIF(expr1,expr2) 的用法： 如果expr1 = expr2 成立，那么返回值为NULL，否则返回值为 expr1。这和CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END相同。 mysql> select nullif(1,1);+-------------+| nullif(1,1) |+-------------+| NULL |+-------------+1 row in set (0.01 sec)mysql> select nullif(1,2);+-------------+| nullif(1,2) |+-------------+| 1 |+-------------+1 row in set (0.01 sec)mysql>如果参数不相等，则 MySQL 两次求得的值为 expr1 。