从表中的行选择第N大/小的值
从表中的行选择第N大/小的值
软件环境
Windows Server 2008 R2
Oracle 11g Release 1 (11.1)
Oracle SCOTT 用户
本文使用 SCOTT 用户的 EMP 表。内容如下：
SQL> select empno,ename,sal
2 from emp
3 order by sal desc;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7902 FORD 3000
7788 SCOTT 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7499 ALLEN 1600
7844 TURNER 1500
7934 MILLER 1300
7521 WARD 1250
7654 MARTIN 1250
EMPNO ENAME SAL
---------- ---------- ----------
7876 ADAMS 1100
7900 JAMES 950
7369 SMITH 800
已选择14行。
SQL>
从表选择第N大的值
SQL> select level, max(sal)
2 from emp
3 where level = '&n'
4 connect by prior sal > sal
5 group by level;
输入 n 的值: 1
原值 3: where level = '&n'
新值 3: where level = '1'
LEVEL MAX(SAL)
---------- ----------
1 5000
SQL> select level, max(sal)
2 from emp
3 where level = '&n'
4 connect by prior sal > sal
5 group by level;
输入 n 的值: 3
原值 3: where level = '&n'
新值 3: where level = '3'
LEVEL MAX(SAL)
---------- ----------
3 2975
SQL>
从表选择第N小的值
SQL> select level, min(sal)
2 from emp
3 where level = '&n'
4 connect by prior sal < sal
5 group by level;
输入 n 的值: 1
原值 3: where level = '&n'
新值 3: where level = '1'
LEVEL MIN(SAL)
---------- ----------
1 800
SQL> select level, min(sal)
2 from emp
3 where level = '&n'
4 connect by prior sal < sal
5 group by level;
输入 n 的值: 5
原值 3: where level = '&n'
新值 3: where level = '5'
LEVEL MIN(SAL)
---------- ----------
5 1300
SQL>
