数据库高级查询

对oracle下默认的scott用户下表的操作（其中3和4,5和6解决同一个问题）：

1.层次查询(上层的员工号等于当前层的老板号)

select level,empno,ename,sal,mgr
from emp
connect by prior empno=mgr
start with mgr is null
order by 1;

2.分页查询:
select rownum,r,empno,ename,sal
from(select rownum r,empno,ename,sal
     from(select rownum,empno,ename,sal from emp order by sal desc) e1
     where rownum<=8) e2
where r>=5;

3.相关子查询 cpu占用：33
explain plan for
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsa from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);

select * from table(dbms_xplan.display);
4.多表查询 cpu占用：30
explain plan for
select e.empno,e.ename,e.sal,d.avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d

where e.deptno=d.deptno and e.sal>d.avgsal;

5.decode、日期处理 cpu占用：6
explain plan for
select count(*) Total,
       sum(decode(to_char(hiredate,'yyyy'),1980,1,0)) "1980",
       sum(decode(to_char(hiredate,'yyyy'),1981,1,0)) "1981",
       sum(decode(to_char(hiredate,'yyyy'),1982,1,0)) "1982",
       sum(decode(to_char(hiredate,'yyyy'),1987,1,0)) "1987"
from emp;

6.伪表、子查询 cpu占用：17
explain plan for
select
      (select count(*) from emp) Total,
      (select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",
      (select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",
      (select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982",
      (select count(*) from emp where to_char(hiredate,'yyyy')='1987') "1987"
from dual;

7.函数：
wm_concat(s.stu_name):结果用逗号隔开
where instr(a,b)>0 :返回字符串b在a中的位置，无则为0