Oracle伪列

Oracle伪列

因为Oracle中没有limit，所以Oracle中使用伪列来进行分页

• 伪列：rownum 它是 orcale 数据库内置列，任何表都可以使用，它的作用是显示表中数据的行号。是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列。简单说就是 rownum 是对符合条件结果的序列号。它总是从1开始排起的。

select emp.*, rownum from emp;

-- 若想进行排序，要先进行排序，然后再添加伪劣
select emp.*, rownum from emp order by sal;	-- false
select t.*, rownum r from (select * from emp order by sal) t; -- true

-- 伪列可以直接小于或小于等于一个值
select emp.*, rownum from emp where rownum<5; -- true
select emp.*, rownum from emp where rownum<=5; -- true

-- 伪列不能直接大于或大于等于一个值
select emp.*, rownum from emp where rownum>5; -- false
select emp.*, rownum from emp where rownum>=5; -- false

-- 伪列不能直接等于一个不等于1的值
select emp.*, rownum from emp where rownum=1; -- true
select emp.*, rownum from emp where rownum=5; -- false

-- 查询5到10名的员工信息
select * from (select emp.*, rownum r from emp) e
where e.r>=5 and e.r<=10;
select * from (select emp.*, rownum r from emp) e
where e.r between 5 and 10;

若要对数据进行分组，然后对分组后的数据进行组内排序，然后组内添加伪列，rownum就不能满足要求了

例如：查询各科成绩前三名的记录。这需要对数据按科目进行分组，分组后的数据不能聚合成一条，然后对分组后的数据在组内进行排序，最后添加伪列。这时group by 就不能满足要求，此时可以使用开窗函数。

• over ([partition by 分组列] ordre by 排序列)：over 子句中的分组partition by和group by的分组不同，它不会把数据聚合成一条，在 over 子句中可以省略
• row_number() ：需要和 over 分析函数联用，排序的序号和 rownum 伪列相同，连续序号，不考虑值相等的情况，同 rownum一样
• dense_rank()：可以用来做排序，它序号连续，考虑重复数据，如果值相等序号就相同
• rank()：可以用来做排序，它序号不连续，考虑重复数据，如果值相等序号相同

-- 序号连续，不考虑重复数据
select e.*, row_number() over (order by sal desc) r from emp e;
-- 序号连续，考虑重复数据
select e.*, dense_rank() over (order by sal desc) r from emp e;
-- 序号不连续，考虑重复数据
select e.*, rank() over (order by sal desc) r from emp e;

-- 分组排序后添加伪列，这里分组不会把数据聚合成一条
select e.*, row_number() over (partition by deptno order by sal desc) r from emp e;
select e.*, dense_rank() over (partition by deptno order by sal desc) r from emp e;
select e.*, rank() over (partition by deptno order by sal desc) r from emp e;

-- 查询各科成绩前三名的记录:(不考虑成绩并列情况)
select s.sno, s.cno, s.score, s.r
from (select sc.*, row_number() over (partition by cno order by score desc) r from sc) s
where s.r<=3;