oracle3
时间：2021-07-01 10:21:17
帮助过：6人阅读
创建视图
create[or replace]
[force|--不管基表是否存在都创建视图。
noforce]--仅当基表存在时才创建视图（默认）。
view view
[(alias[,alias]…)]
as subquery
--子查询，决定了视图对应的数据。
[with check option[constraint constraint]]
--指明对视图操作时，必须满足子查询中的约束条件。
[with read only[constraint constraint]];
--指明该视图为只读视图，不能修改。
--创建TOP-N视图
--内嵌视图有一种特殊的应用，称为Top-N-Analysis查询，通过使用伪列ROWNUM，为查询结果集排序，并返回符合条件的记录。
select[column_list],rownum
from(
select[column_list]from table order by top-n_column)
where rownum＜
=n;
--例1
select * from
(
select employee_id,first_name,last_name,salary
from employee
order by salary
desc
)nested_order where rownum
<= 5;
--查询工资排在前1~5名的员工号，员工名，工资，及其工资排号。
--例2
select * from t_test4
where rownum
<= 5;
--也是可以的
--例3
select * from
(
select rownum num,employee_id,first_name,last_name,salary
from
(
select employee_id,first_name,last_name,salary
from employee
order by salary
desc
· )nested_order1
)nested_order2 where num
between 5 and 10;
--查询工资排在前5~10名的员工号，员工名，工资，及其工资排号。
--那么问题来了，为什么内嵌查询可以rownum>1
--分页查询
--第一种方式
select * from
(
select a.
*, rownum
as rn
from css_bl_view a
where capture_phone_num
= ‘(1) 925-4604800‘
)b where b.rn
between 6 and 10;
--第二种方式
select * from css_bl_view a
where capture_phone_num
= ‘(1) 925-4604800‘ and rownum
<= 10
minus
select * from css_bl_view a
where capture_phone_num
= ‘(1) 925-4604800‘ and rownum
<= 5;
--第三种方式
select * from
(
select a.
*, rownum
as rn
from css_bl_view a
where capture_phone_num
= ‘(1) 925-4604800‘ and rownum
<= 10
)b where b.rn
> 5;
--一个用rownum的小技巧
select decode(rownum
-min_sno,
0,a.owner,
null) owner,decode(rownum
-min_sno,
0,
1,rownum
+1-min_sno) sno, a.name
from (
select * from t_test8
order by owner, name ) a,
(select owner,
min(rownum) min_sno
from(
select * from t_test8
order by owner, name)
group by owner) b
where a.owner
=b.owner;
oracle3
标签：返回 数据 between phone -- apt 存在 min rownum
