oracle over函数用法
1，初始化脚本create table emp(deptno varchar2(10),ename varchar2(20),sal number(10,0));insert into emp(deptno,ename,sal)values('20',trim('ADAMS '), 1100 );insert into emp(deptno,ename,sal)values('30',trim('ALLEN '), 1600 );insert into emp(deptno,ename,sal)values('30',trim('BLAKE '), 2850 );insert into emp(deptno,ename,sal)values('10',trim('CLARK '), 2450 );insert into emp(deptno,ename,sal)values('20',trim('FORD '), 3000 );insert into emp(deptno,ename,sal)values('30',trim('JAMES '), 950 );insert into emp(deptno,ename,sal)values('20',trim('JONES '), 2975 );insert into emp(deptno,ename,sal)values('10',trim('KING '), 5000 );insert into emp(deptno,ename,sal)values('30',trim('MARTIN'), 1250 ); insert into emp(deptno,ename,sal)values('10',trim('MILLER'), 1300 ); insert into emp(deptno,ename,sal)values('20',trim('SCOTT '), 3000 );insert into emp(deptno,ename,sal)values('20',trim('SMITH '), 800 );insert into emp(deptno,ename,sal)values('30',trim('TURNER'), 1500 ); insert into emp(deptno,ename,sal)values('30',trim('WARD '), 1250 );
2，具体用法
select * from (select deptno, ename, sal, --sum(sal) over(partition by deptno order by sal) --先按deptno分组，然后在分组内按sal排序逐个累加sal --sum(sal) over(order by deptno,ename)--按deptno，ename排序，逐个累加sal --sum(sal) over(order by ename)--按ename排序，逐个累加sal --sum(sal) over() --相当于sum(sal) --sum(sal) over (partition by deptno order by ename desc,sal desc)--先按deptno分组，然后在分组内按ename,sal倒序排序逐个累加sal row_number() over(/*partition by deptno*/ order by sal desc) row_n --按sal倒序排序得到行号from emp )where row_n between 1 and 3; --分页查询
3，分页方法
-- 正确方法1select *from (select rownum row_num, a.* from (select t.* from emp t order by t.sal) a) b where b.row_num between 1 and 3;-- 正确方法2，比方法1好一些select *from (select rownum row_num, a.* from (select t.* from emp t order by t.sal) a where rownum <= 3) b where b.row_num >= 1;-- 正确方法3，这三种分页方法性能几乎相等，如果非要分个高低：方法2 > 方法1 > 方法3，但方法3的写法最简洁，只需嵌套一层，前两种方法都必须嵌套两层select *from (select row_number() over(order by t.sal) row_num, t.* from emp t) a where a.row_num between 1 and 3;-- 错误方法4，这是典型的错误分页方法，数据库会先把数据select出来，这时rownum伪列根据默认顺序已经分配了数值，然后再进行order by，这样row_num顺序已经被打乱，此时的查询结果和order by前是一样的，依赖数据库默认select的结果顺序，一般数据库不保证每次select出来的顺序是一致的（当然绝大多数情况下是一致的，除非表数据做了整理）select *from (select rownum row_num, t.* from emp t order by t.sal) a where a.row_num between 1 and 3;
页:
[1]