伪列
--伪列(针对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)
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)
--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)
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
