伪列

--伪列(针对Oracle数据库)


--rowid
--rownum
--level


--rowid
--去重
主键    列1   列2  列3
1        1     x   y
2        1     x   y
3        3     w   w   
4        4     q   q
5        4     q   q
6        5     p   p
delete from 表X
where rowid not in(select max(rowid) 表X group by 列1,列2,列3)

--rownum
select rownum,t.* from emp t
where rownum between 1 and 5

select * from emp limit 0,5（MySQL中可以，oracle不支持）

--rownum只能从一开始
select * from (select rownum as num,t.* from emp t)

where num between 6 and 10    

--top-N分析
--按工资排序
select * from (select rownum as num,t.* from emp  t order by sal desc)
where num between 1 and 5 
--发现第一不是King，这是由于num的添加在排序之前造成的   所以应该先排序，再嵌套个表


select * from (select rownum as num,t.* from (select * from emp order by sal desc) t)
where num between 1 and 5 


--工资前三的人信息
select * from (select * from emp order by sal desc)
where rownum <=3

--level  递归查询
select * from emp 
connect by | start with


--查询当前行mgr=前一行员工编号
select * from emp 
connect by mgr=prior empno   --递归条件  不满足即结束
start with mgr is null       --起止点


--level     --区分层级之间的关系
select level,t.* from emp t
connect by mgr=prior empno   
start with mgr is null