Mysql数据库(0)习题分析

  1.查询表中第二高工资的Id,如果没有,返回NULL。此题的关键是如果遇到Empty set,就必须要返回NULL。

  (1)使用子查询。

select (select DISTINCT salary from employee ORDER BY salary DESC limit 1 offset 1) AS SecondHighestSalary;

  select(子查询)as result中,如果子查询是Empty set,那么得到的结果就是result下面一个NULL。

  注意:select(子查询)as result和select * from (子查询)as result的区别,前者只有一次子查询,后者是在括号中子查询得到的结果中再次查询,有两次查询。

mysql> select * from employee;
+------+------+--------+-----------+
| Id   | Name | Salary | ManagerId |
+------+------+--------+-----------+
|    1 | NULL |    100 |      NULL |
+------+------+--------+-----------+
1 row in set (0.00 sec)

mysql> select * from (select * from employee where id = 1) as ca;
+------+------+--------+-----------+
| Id   | Name | Salary | ManagerId |
+------+------+--------+-----------+
|    1 | NULL |    100 |      NULL |
+------+------+--------+-----------+
1 row in set (0.00 sec)

mysql> select * from (select * from employee where id = 2) as ca;
Empty set (0.00 sec)

mysql> select (select * from employee where id = 1) as ca;
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select (select salary from employee where id = 1) as ca;
+------+
| ca   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

mysql> select (select salary from employee where id = 2) as ca;
+------+
| ca   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> select (select salary,Name from employee where id = 2) as ca,ba;
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select (select salary from employee where id = 2);
+--------------------------------------------+
| (select salary from employee where id = 2) |
+--------------------------------------------+
|                                       NULL |
+--------------------------------------------+
1 row in set (0.00 sec)
View Code

  (2)使用IFNULL语句。

  IFNULL(value1,value2) 如果 value1 不为空返回 value1,否则返回 value2

select IFNULL((select DISTINCT salary from employee ORDER BY salary DESC limit 1 offset 1), null) AS SecondHighestSalary;

  弄清select的作用:

mysql> select (null) as shit;
+------+
| shit |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

 

  2.

posted @ 2018-08-29 22:59  BigJunOba  阅读(175)  评论(0编辑  收藏  举报