高级子查询
[@more@]SQL> select ename,sal,deptno from emp e2 where sal>(select avg(sal) from emp where deptno=e.deptno);
ENAME SAL DEPTNO
---------- ---------- ----------
ALLEN 1600 30
JONES 2975 20
BLAKE 2850 30
SCOTT 3000 20
KING 5000 10
FORD 3000 20
已選取 6 個資料列.
外查询中的行每被处理一次，内查询就求值一次.
=================
SQL> run
1 select * from dept d
2 where not exists(
3* select 'X' from emp where d.deptno=deptno)
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
50 SFIS CHINA
---------------------------
SQL> select * from dept t where deptno not in(
2 select deptno from emp );
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
50 SFIS CHINA
=================
with 子句
当一个查询块在一个复杂的查询中出现多次时，使用 WITH 子句，能够用在 SELECT 语句中使用相同查询块
WITH 子句取回查询块的结果，并且将它存在用户的临时表空间中
WITH 子句可以改善性能
Example:
SQL> run
1 with
2 dept_costs as(
3 select d.dname ,sum(sal) as dept_total
4 from emp e,dept d
5 where e.deptno=d.deptno
6 group by d.dname),
7 avg_cost as(
8 select sum(dept_total)/count(*) as dept_avg from dept_costs)
9 select *
10 from dept_costs
11 where dept_total>(select dept_avg from avg_cost)
12* order by dname
DNAME DEPT_TOTAL
-------------- ----------
RESEARCH 10875
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9925929/viewspace-915627/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
