Mysql数据库（0）习题分析
2024-09-01 12:28:29
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)
（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.
最新文章
- [Visual Studio Online] 移除Work Item(Feature、Backlog item、Task)
- 更新安装xcode7插件
- jquery easyui无法绑定下拉框内容
- 感动前行——给医学媳妇写的演讲稿(非IT类）
- Get Start StrangeIOC for Unity3D
- 使用RNSwipeViewController类库进行视图切换
- Angularjs里面跨作用域
- ABAP Open SQL 分页查询
- springboot项目容器化
- Git知识
- FreeType使用的总结
- python 子进程 subpocess 的使用方法简单介绍
- ORM创建多表以及多表的增删改查
- [HEOI2015]小Z的房间 && [CQOI2018]社交网络
- 遇见 TiDB
- vue全局后置钩子afterEach
- 获取验证码的URL后边为什么要加上一个值不断变化的参数？
- Java学习--基本数据类型的定义和运算
- [转载] PNG优化插件：TinyPNG for Photoshop CC
- macbook安装homebrew并更换清华源