Oracle数据库SQL语句(四)
子查询(嵌套查询)
1、单行子查询
SELECT * FROM stuWHERE ID > (SELECT ID FROM stu WHERE NAME ='王五');
2、多行子查询
--IN 在什么范围内SELECT * FROM stuWHERE ID IN (SELECT ID FROM stu WHERE CLASS = '一班');--ANY:表示任意的--= ANY :和子查询中任意一个结果相等即可,相当于IN;SELECT * FROM stuWHERE ID = ANY ( SELECT ID FROM stu WHERE CLASS = '一班');--< ANY :比子查询返回的任意一个结果小即可,即小于返回结果SELECT * FROM stuWHERE ID < ANY ( SELECT ID FROM stu WHERE CLASS = '一班');--> ANY :比子查询返回的任意一个结果大即可,即大于返回结果SELECT * FROM stuWHERE ID > ANY ( SELECT ID FROM stu WHERE CLASS = '一班');
--ALL:表示所有的--= ALL :无意义,逻辑上也不成立。SELECT * FROM stuWHERE ID = ALL ( SELECT ID FROM stu WHERE CLASS = '一班');--< ALL :比子查询返回的所有的结果都小,即小于返回结果的最小值SELECT * FROM stuWHERE ID < ALL ( SELECT ID FROM stu WHERE CLASS = '三班');--> ALL :比子查询返回的所有的结果都大,即大于返回结果的最大值SELECT * FROM stuWHERE ID > ALL ( SELECT ID FROM stu WHERE CLASS = '三班');
3、多列子查询
--多列子查询SELECT ID,NAME,CLASS FROM stuWHERE ID IN (SELECT MAX(ID) FROM stu GROUP BY CLASS);
4、相关子查询
--相关子查询中,内部查询需引用外部查询的列,进行交互判断。--相关子查询的执行方式是一行行操作。--外部查询每执行一行操作,内部查询都要执行一次。SELECT ID,NAME,CLASS FROM stuWHERE ID > (SELECT AVG(ID) FROM stu WHERE CLASS = '三班');