数据库高级查询

对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
阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/z595746437/article/details/77968801
文章标签: 数据库
个人分类: 数据库,查询语句
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭