oracle中max函数的使用方法,oracle MAX/MIN 分析函数小结

1、通常用法：

select

deptno,empno,ename,sal,

MIN(SAL) OVER(partition by deptno order

by hiredate) minsal,

MAX(SAL) OVER(partition by deptno order

by hiredate) maxsal

fromemp;

DEPTNO EMPNO

ENAMESALMINSALMAXSAL

------ -----

---------- --------- ---------- ----------

107782 CLARK2450.0024502450

107839 KING5000.0024505000

107934 MILLER1300.0013005000

207369 SMITH800.00800800

207566 JONES2975.008002975

207902 FORD3000.008003000

207788 SCOTT3000.008003000

207876 ADAMS1100.008003000

307499 ALLEN1600.0016001600

307521 WARD1250.0012501600

307698 BLAKE2850.0012502850

307844 TURNER1500.0012502850

307654 MARTIN1250.0012502850

307900 JAMES950.009502850

查出在本分区内当前行或当前行以上范围内的最大和最小值。

需要注意的是：

1、不加partition，则是从第一行开始，maxsal的值保持不变，直到碰到比它大的值为止，然后又变成较大的值，

再一直往下寻找。

DEPTNO EMPNO

ENAMESALMINSALMAXSAL

------ -----

---------- --------- ---------- ----------

207369 SMITH800.00800800

307499 ALLEN1600.008001600

307521 WARD1250.008001600

207566 JONES2975.008002975

307698 BLAKE2850.008002975

107782 CLARK2450.008002975

307844 TURNER1500.008002975

307654 MARTIN1250.008002975

107839 KING5000.008005000

307900 JAMES950.008005000

207902 FORD3000.008005000

107934 MILLER1300.008005000

2、如果没有orderby，则在全表搜索：

DEPTNO EMPNO

ENAMESALMINSALMAXSAL

------ -----

---------- --------- ---------- ----------

207369 SMITH800.008005000

307499 ALLEN1600.008005000

307521 WARD1250.008005000

207566 JONES2975.008005000

307654 MARTIN1250.008005000

307698 BLAKE2850.008005000

107782 CLARK2450.008005000

207788 SCOTT3000.008005000