oracle查询笔记(2)
来源:互联网 发布:js注册自定义事件 编辑:程序博客网 时间:2024/05/17 21:57
11.在多行子查询中使用all操作符
all表示所有,用在子句中
select ename,sal from emp where sal>all(select sal from emp where deptno=30);
等同于
select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);
12.多行子查询中使用any操作符
select ename,sal from emp where sal>any(select sal from emp where deptno=30);
等同于
select ename,sal from emp where sal>(select min(sal) from emp where deptno=30);
13.多列子查询
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
14.oracle分页
1)rownum分页
select * from
(select rownum rn,a1.* from emp a1 where rownum<9)
where rn>4;
注意:所有的改动都只改里面的查询
2)rowid分页(效率是rownum的3倍)
select * from emp where rowid in
(select rid from
(select rownum rn,rid from
(select rowid rid,empno from emp order by empno desc)
where rownum<10)
where rn>3)
order by empno desc;
15.如何显示高于自己部门平均工资的员工的信息
select a1.* from emp a1,(select deptno,avg(sal) avgsal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sal>a2.avgsal;
16.用查询结果创建新表
create table emp2 (empno,ename,sal) as
select empno,ename,sal from emp;
all表示所有,用在子句中
select ename,sal from emp where sal>all(select sal from emp where deptno=30);
等同于
select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);
12.多行子查询中使用any操作符
select ename,sal from emp where sal>any(select sal from emp where deptno=30);
等同于
select ename,sal from emp where sal>(select min(sal) from emp where deptno=30);
13.多列子查询
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
14.oracle分页
1)rownum分页
select * from
(select rownum rn,a1.* from emp a1 where rownum<9)
where rn>4;
注意:所有的改动都只改里面的查询
2)rowid分页(效率是rownum的3倍)
select * from emp where rowid in
(select rid from
(select rownum rn,rid from
(select rowid rid,empno from emp order by empno desc)
where rownum<10)
where rn>3)
order by empno desc;
15.如何显示高于自己部门平均工资的员工的信息
select a1.* from emp a1,(select deptno,avg(sal) avgsal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sal>a2.avgsal;
16.用查询结果创建新表
create table emp2 (empno,ename,sal) as
select empno,ename,sal from emp;
- oracle查询笔记(2)
- oracle笔记2-sql查询
- Oracle笔记多表查询(2)
- oracle表的查询学习笔记2
- oracle系列笔记(2)---多表查询
- oracle笔记三-查询
- oracle 查询笔记
- Oracle笔记之子查询
- Oracle 层次查询-学习笔记
- oracle笔记第三章----查询
- Oracle笔记 :基本查询(select)
- Oracle笔记:层次查询(Select)
- oracle过程查询学习笔记
- oracle学习笔记--复杂查询
- Oracle笔记之合并查询
- Oracle笔记之分页查询
- oracle常见查询转换笔记
- oracle学习笔记 ---- 子查询
- fork()函数
- python实现电子邮件(E-mail)发送
- ICTCLAS2011 java UnsatisfiedLinkError ICTCLAS2011.ICTCLAS_Init([BI)Z
- 在一个字符串中找到第一个只出现一次的字符。如输入abaccdeff,则输出b
- tomcat中文问题的解决
- oracle查询笔记(2)
- oracle grouping sets
- 00000010
- 在oracle中函数NVL是什么意思?
- Linux Qt 4.7.4 编译
- 孙鑫-MFC笔记十--修改应用程序外观
- JQuery文档信息
- 作品展总结三……学习汇报
- 7.组合查询及子查询