ORACLE SQL 高级子查询

小洪維尼

　　第十八章:高级子查询
　　内外交互式子查询:
　　select ename,sal
　　from emp e
　　where sal>(select avg(sal) from emp where deptno=e.deptno);
　　存在否:exists
　　select ename
　　from emp
　　where empno in (select distinct mgr from emp);
　　select ename
　　from emp e
　　where exists
　　(select 'x' from emp where mgr=e.empno);
　　select ename
　　from emp e
　　where not exists
　　(select 'x' from emp where mgr=e.empno);
　　关联更新:
　　drop table e purge;
　　create table e as select * from emp;
　　alter table e add (location varchar2(13));
　　将正确的城市的信息添加到location列!
　　update e set location=
　　(select loc from dept where deptno=e.deptno);
　　关联删除:
　　从e表中删除销售部门的员工！
　　delete e where deptno=
　　(select deptno from dept where deptno='SALES');
　　with子句:
　　哪个部门总工资大于所有部门的平均工资!
　　SELECT *
　　FROM
　　(SELECT d.dname, SUM(e.sal) AS dept_total
　　FROM emp e, dept d
　　WHERE e.deptno = d.deptno
　　GROUP BY d.dname)
　　where dept_total>
　　(SELECT SUM(dept_total)/COUNT(*) AS dept_avg
　　FROM (SELECT d.dname, SUM(e.sal) AS dept_total
　　FROM emp e, dept d
　　WHERE e.deptno = d.deptno
　　GROUP BY d.dname));
　　WITH
　　dept_costs AS (
　　SELECT d.dname, SUM(e.sal) AS dept_total
　　FROM emp e, dept d
　　WHERE e.deptno = d.deptno
　　GROUP BY d.dname),
　　avg_cost AS (
　　SELECT SUM(dept_total)/COUNT(*) AS dept_avg
　　FROM dept_costs)
　　SELECT *
　　FROM dept_costs
　　WHERE dept_total >
　　(SELECT dept_avg
　　FROM avg_cost)
　　ORDER BY dname;
　　with子句中的视图叫做询问块，询问块的复杂查询在
　　with子句中只运行一次，运行成功后会将询问块的结果集
　　保存到temp表空间，以后再次调用询问块时会在后台转换
　　为对结果集的直接访问
　　使连续重复的值只显示一次:
　　with test as (
　　select 1 cd,'a' name from dual union all
　　select 1 cd,'b' name from dual union all
　　select 1 cd,'c' name from dual union all
　　select 1 cd,'d' name from dual union all
　　select 1 cd,'e' name from dual union all
　　select 2 cd,'q' name from dual union all
　　select 2 cd,'w' name from dual union all
　　select 2 cd,'e' name from dual)
　　select
　　(case when
　　(row_number() over(partition by cd order by name))=1
　　then cd else null end) as cd,name from test;