oracle的break命令排列检索结果

来源：互联网发布：研究人工智能的意义编辑：程序博客网时间：2024/05/17 21:38

用 BREAK ON column SKIP xx 来对查询结果进行排列，BREAK ON 命令的参数如下：
clear breaks 清除所有的 break 定义
break on column 在该列上中断
break on row 在每一行上中断
break on Page 在每一页上中断
break on report 在每一报告上中断
skip n 跳过n行

skip page 跳过未用完的页

示例：

SQL> break on deptno skip 1;
SQL> select deptno, ename,sal,
2 row_number()
3 over (partition by deptno
4 order by sal desc)rn,
5 rank()
6 over (partition by deptno
7 order by sal desc)rnk,
8 dense_rank()
9 over (partition by deptno
10 order by sal desc)drnk
11 from emp
12 order by deptno, sal desc;

DEPTNO ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ---------- ----------
10 KING 5000 1 1 1
CLARK 2450 2 2 2
MILLER 1300 3 3 3

20 SCOTT 3000 1 1 1
FORD 3000 2 1 1
JONES 2975 3 3 2
ADAMS 1100 4 4 3
SMITH 800 5 5 4

30 BLAKE 2850 1 1 1
ALLEN 1600 2 2 2
TURNER 1500 3 3 3
MARTIN 1250 4 4 4
WARD 1250 5 4 4
JAMES 950 6 6 5

未用break命令排列时，检索结果为：

SQL> select deptno, ename,sal,
2 row_number()
3 over (partition by deptno
4 order by sal desc)rn,
5 rank()
6 over (partition by deptno
7 order by sal desc)rnk,
8 dense_rank()
9 over (partition by deptno
10 order by sal desc)drnk
11 from emp
12 order by deptno, sal desc;

DEPTNO ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ---------- ----------
10 KING 5000 1 1 1
10 CLARK 2450 2 2 2
10 MILLER 1300 3 3 3
20 SCOTT 3000 1 1 1
20 FORD 3000 2 1 1
20 JONES 2975 3 3 2
20 ADAMS 1100 4 4 3
20 SMITH 800 5 5 4
30 BLAKE 2850 1 1 1
30 ALLEN 1600 2 2 2
30 TURNER 1500 3 3 3
30 MARTIN 1250 4 4 4
30 WARD 1250 5 4 4
30 JAMES 950 6 6 5