不等于号用了不走索引 效率很低
分页row_number要求emp表中的5-10的记录?
select *from(select a.*,row_number() over(order by empno desc) rk from emp a)where rk<=10 and rk>=5;
select rn,empno,enamefrom (select rownum rn,empno,ename from emp)where rn>=5 and rn<=10;
RN EMPNO ENAME
---------- ---------- ----------
5 7566 JONES
6 7654 MARTIN
7 7698 BLAKE
8 7782 CLARK
9 7788 SCOTT
10 7839 KING
6 rows selected.
select *from (select deptno,ename,sal,dense_rank()over(partition by deptno order by sal desc) rkfrom emp)where rk<=3;--->发现部门为20的scott和ford都是3k,所以并列第一 有2.3名
DEPTNO ENAME SAL RK
---------- ---------- ---------- ----------
10 KING 5000 1
10 CLARK 2450 2
10 MILLER 1300 3
20 SCOTT 3000 1
20 FORD 3000 1
20 JONES 2975 2
20 ADAMS 1100 3
30 BLAKE 2850 1
30 ALLEN 1600 2
30 TURNER 1500 3
CLERK 1
11 rows selected.
select *from (select deptno,ename,sal,rank()over(partition by deptno order by sal desc) rkfrom emp )where rk<=3;--->发现部门为20的scott和ford都是3k,所以并列第一 没2有3名
DEPTNO ENAME SAL RK
---------- ---------- ---------- ----------
10 KING 5000 1
10 CLARK 2450 2
10 MILLER 1300 3
20 SCOTT 3000 1
20 FORD 3000 1
20 JONES 2975 3
30 BLAKE 2850 1
30 ALLEN 1600 2
30 TURNER 1500 3
CLERK 1
10 rows selected.
Partition by == group by
分析函数里用的是partition by
分析函数的一般格式是:
函数名(参数列表) over ([partition by 字段名或表达式] [order by 字段名或表达式]),其中over()部分称为开窗函数,它是可以选填的。
SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;
列出Col2分组后根据Col1排序,并生成数字列。
普通函数 用的是 group by
select *
from(
select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) rn
from emp)
where rn<=3;--->发现部门为20的scott和ford按照默认显示出现1和2名次
那么看下这些区别:
select *from(select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) rn,rank() over(partition by deptno order by sal desc) rk,dense_rank() over(partition by deptno order by sal desc) drkfrom emp)where drk<=3;
由此结论:
rank()跳跃排序,比如有两个第1名时接下来就是第3名(同样是在各个分组内)
dense_rank()连续排序,比如有两个第1名时仍然跟着第2名。