• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

Oracle子查询与分页查询

开发技术 开发技术 2天前 6次浏览

子查询

子查询是指嵌套在查询语句中的查询语句。子查询出现的位置一般为条件语句,如WHERE条件。Oracle会首先执行子查询,然后执行父查询。

子查询整个结果集会和父结果集中每个结果进行预算,通常效率比较低,因此建议在实际应用中如果能够不使用子查询则尽量较少使用。

 

查询复制填充表

查询复制数据填充新建表

CREATE TABLE backupStu AS SELECT name,age FROM students2

 

查询复制数据填充存在表

INSERT INTO copyTab SELECT name,age FROM students2

 

分页查询

分页的目的在于将过多符合条件的结果记录按照自定义数量显示数据,从而减少内存开销并提高查询效率

Oracle中通常使用联合、子查询以及结合伪列rowidrownum实现对结果进行分页

 

minus结合rownum查询分页

select * from students2 where rownum <=5 minus

select * from students2 where rownum <1 —1

 

select * from students2 where rownum <=10 minus

select * from students2 where rownum <6 —2

 

select * from students2 where rownum <=15 minus

select * from students2 where rownum <11 —3

 

select * from students2 where rownum <=20 minus

select * from students2 where rownum <16 —4

 

子查询结合rownum查询分页

select * from(

       (select rownum rn,s2.* from (select * from salary )s2 where rownum <=5)) s3

where s3.rn >=1;       —1

 

select * from(

       (select rownum rn,s2.* from (select * from salary )s2 where rownum <=10)) s3

where s3.rn >=6;       —2

 

select * from(

       (select rownum rn,s2.* from (select * from salary )s2 where rownum <=15)) s3

where s3.rn >=11;       —3

 

 

--子查询
--哪些部门没有员工
select ID 部门id,NAME 部门名称 FROM dep_table where id not in (select dep_id from emp_tab );

--哪些员工发放过工资
select ID,NAME FROM emp_tab WHERE ID IN (select emp_id from salary);
--哪些员工未发放过工资
select ID,NAME FROM emp_tab WHERE ID NOT IN (select emp_id from salary);

--查询数据填充新建表
SELECT * FROM salary;
DROP TABLE backupsalary;

CREATE TABLE backupSalary AS
SELECT emp_id ID,grantdate 发放年月,should 应发工资 FROM salary s;

select 应发工资 from backupSalary;

--查询数据填充已存在的表

CREATE TABLE back_sal
(
 ID NUMBER NOT NULL PRIMARY KEY,
 yingfa NUMBER
)

INSERT INTO back_sal select ID,salary.should from salary;
select * from back_sal;

--minus 结合rownum实现分页查询
select * from salary;
--第1页
select * from salary where rownum <=5
MINUS SELECT * FROM salary WHERE ROWNUM<1;
--第2页
select * from salary where rownum <=10
MINUS SELECT * FROM salary WHERE ROWNUM<6;
--第3页
select * from salary where rownum <=15
MINUS SELECT * FROM salary WHERE ROWNUM<11;

--子查询结合rownum实现分页查询
select * from(
       (select rownum rn,s2.* from (select * from salary )s2 where rownum <=5)) s3
where s3.rn >=1;       --第1页

select * from(
       (select rownum rn,s2.* from (select * from salary )s2 where rownum <=10)) s3
where s3.rn >=6;       --第2页

select * from(
       (select rownum rn,s2.* from (select * from salary )s2 where rownum <=15)) s3
where s3.rn >=11;       --第3页
       
--使用子查询结合rowid,rownum not in语句实现分页
select * from salary where rowid not in (select rowid from salary
where rownum<1) and rownum <=5; --第一页

select * from salary where rowid not in (select rowid from salary
where rownum<6) and rownum <=5; --第二页

select * from salary where rowid not in (select rowid from salary
where rownum<11) and rownum <=5; --第三页

 


程序员灯塔
转载请注明原文链接:Oracle子查询与分页查询
喜欢 (0)