【oracle】子查询

SELECT * FROM EMP

WHERE SAL IN(

 SELECT MIN(SAL) FROM EMP       --用IN进行统计查询

 GROUP BY DEPTNO

);

 

SELECT * FROM EMP

WHERE SAL NOT IN(

 SELECT MIN(SAL) FROM EMP       --用IN进行统计查询

 GROUP BY DEPTNO

);

 

SELECT * FROM EMP

WHERE SAL=ANY(

  SELECT MIN(SAL)FROM EMP       --=ANY与IN一样，<>ANY不等价于NOTIN,就是将表中的数据全部返回，没有什么意义

 GROUP BY DEPTNO

);

 

SELECT * FROM EMP

WHERE SAL>ANY(

  SELECT MIN(SAL)FROM EMP                -->ANY,返回的结果>子查询结果的最小值

 GROUP BY DEPTNO

);

 

SELECT * FROM EMP

WHERE SAL<ANY(

  SELECT MIN(SAL)FROM EMP                --<ANY,返回的结果<子查询结果的最大值

 GROUP BY DEPTNO

);

 

SELECT * FROM EMP

WHERE SAL<>ALL(

  SELECT MIN(SAL)FROM EMP                --<>ALL与NOT IN等价，但是=ALL却不等价于IN

 GROUP BY DEPTNO

);

 

SELECT * FROM EMP

WHERE SAL=ALL(

 SELECT MIN(SAL) FROM EMP       --用=ALL不会返回任何结果

 GROUP BY DEPTNO

);

 

SELECT * FROM EMP

WHERE SAL>ALL(

 SELECT MIN(SAL) FROM EMP       --用>ALL结果比子查询中的最大值还要大

 GROUP BY DEPTNO

);

 

SELECT * FROM EMP

WHERE SAL<ALL(

 SELECT MIN(SAL) FROM EMP       --用<ALL结果比子查询中的最小值还要小

 GROUP BY DEPTNO

);

 

SELECT * FROM EMP

WHERE EXISTS(                     --用EXISTS判断子查询中是否右结果返回

 SELECT * FROM EMP WHERE EMPNO=7369

);

 

SELECT DEPTNO,COUNT(EMPNO) COUNT,AVG(SAL)AVG

FROM EMP

GROUP BY DEPTNO          --在HAVING中使用子查询

HAVING AVG(SAL)>(

SELECT AVG(SAL) FROM EMP

);

 

SELECT D.DNAME,ROUND(AVG(E.SAL),2)

FROM EMP E,DEPT D

WHERE E.DEPTNO=D.DEPTNO

GROUP BY D.DNAME

HAVING AVG(SAL)=(                --查询出每个部门中平均工资最高的部门名称及平均工资

 SELECT MAX(AVG(SAL))

 FROM EMP

 GROUP BY DEPTNO

);

 

SELECTD.DEPTNO,D.DNAME,D.LOC,TEMP.COUNT,TEMP.AVG

FROM DEPT D,(

 SELECT DEPTNO DNO,COUNT(EMPNO) COUNT,ROUND(AVG(SAL),2) AVG

 FROM EMP                  --在FROM字句中的子查询，临时表必须取别名

 GROUP BY DEPTNO) TEMP

WHERE D.DEPTNO=TEMP.DNO(+);