关闭

数据库高级查询

标签: 数据库
31人阅读 评论(0) 收藏 举报
分类:

对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
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:138次
    • 积分:42
    • 等级:
    • 排名:千里之外
    • 原创:1篇
    • 转载:0篇
    • 译文:0篇
    • 评论:0条
    文章分类
    文章存档