1.工资排序后取第一行到当前行范围内的最小值
select ename,
sal,
/*因是按工资排序，所以这个语句返回的结果就是所有行的最小值*/
min(sal) over(order by sal) as min_11,
/*上述语句默认参数如下，plan中可以看到*/
min(sal) over(order by sal range between unbounded preceding and current row) as min_12,
/*这种情况下，rows与range返回数据一样*/
min(sal) over(order by sal rows between unbounded preceding and current row) as min_13,
/*取所有行内最小值，可以与前面返回的值对比查看*/
min(sal) over() as min_14,
/*如果明确写出上面min_14的范围就是*/
min(sal) over(order by sal range between unbounded preceding and unbounded following) as min_15,
/*这种情况下，rows与range返回数据一样*/
min(sal) over(order by sal rows between unbounded preceding and unbounded following) as min_16
from emp
where deptno=30;
ENAME SAL MIN_11 MIN_12 MIN_13 MIN_14 MIN_15 MIN_16
—————————— ———- ———- ———- ———- ———- ———- ———-
JAMES 950 950 950 950 950 950 950
WARD 1250 950 950 950 950 950 950
MARTIN 1250 950 950 950 950 950 950
TURNER 1500 950 950 950 950 950 950
ALLEN 1600 950 950 950 950 950 950
BLAKE 2850 950 950 950 950 950 950
6 rows selected.
2.工资排序后取第一行到当前行范围内的最大值
select ename,
sal,
/*因是按工资排序，所以这个语句与上面sal返回的值一样*/
max(sal) over(order by sal) as max_11,
/*上述语句默认参数如下，plan中可以看到*/
max(sal) over(order by sal range between unbounded preceding and current row) as max_12,
/*这种情况下，rows与range返回数据一样*/
max(sal) over(order by sal rows between unbounded preceding and current row) as max_13,
/*取所有行内最大值，可以与前面返回的值对比查看*/
max(sal) over() as max_14,
/*如果明确写出上面max_14的范围就是*/
max(sal) over(order by sal range between unbounded preceding and unbounded following) as max_15,
/*这种情况下，rows与range返回数据一样*/
max(sal) over(order by sal rows between unbounded preceding and unbounded following) as max_16
from emp
where deptno=30;
ENAME SAL MAX_11 MAX_12 MAX_13 MAX_14 MAX_15 MAX_16
—————————— ———- ———- ———- ———- ———- ———- ———-
JAMES 950 950 950 950 2850 2850 2850
WARD 1250 1250 1250 1250 2850 2850 2850
MARTIN 1250 1250 1250 1250 2850 2850 2850
TURNER 1500 1500 1500 1500 2850 2850 2850
ALLEN 1600 1600 1600 1600 2850 2850 2850
BLAKE 2850 2850 2850 2850 2850 2850 2850
6 rows selected.
3.工资排序后取第一行到当前行范围内的工资和，这里要注意区别。
select ename,
sal,
/*累加工资，要注意工资重复时的现象*/
sum(sal) over(order by sal) as sum_11,
/*上述语句默认参数如下，plan中可以看到*/
sum(sal) over(order by sal range between unbounded preceding and current row) as sum_12,
/*这种情况下，rows与range返回数据不一样，见第二行*/
sum(sal) over(order by sal rows between unbounded preceding and current row) as sum_13,
/*工资合计*/
sum(sal) over() as sum_14,
/*如果明确写出上面sum_14的范围就是*/
sum(sal) over(order by sal range between unbounded preceding and unbounded following) as sum_15,
/*这种情况下，rows与range返回数据不一样*/
sum(sal) over(order by sal rows between unbounded preceding and unbounded following) as sum_16
from emp
where deptno=30;
ENAME SAL SUM_11 SUM_12 SUM_13 SUM_14 SUM_15 SUM_16
—————————— ———- ———- ———- ———- ———- ———- ———-
JAMES 950 950 950 950 9400 9400 9400
WARD 1250 3450 3450 2200 9400 9400 9400
MARTIN 1250 3450 3450 3450 9400 9400 9400
TURNER 1500 4950 4950 4950 9400 9400 9400
ALLEN 1600 6550 6550 6550 9400 9400 9400
BLAKE 2850 9400 9400 9400 9400 9400 9400
6 rows selected.
因为使用关键字‘RANGE’时，第二行‘SUM_11’、‘SUM_12’对应的条件是‘<=1250’，而1250有两个，所以会计算两次，产生的结果为：950+1250+1250=3450。而‘SUM_13‘不同，它只计算到当前行，所以结果是950+1250=2200。
4.前后都有限定条件
select ename,
sal,
/*当前行（+-500）范围内的最大值*/
max(sal) over(order by sal range between 500 preceding and 500 following) as max_11,
/*前后各一行，共三行中的最大值*/
max(sal) over(order by sal rows between 1 preceding and 1 following) as max_12
from emp
where deptno=30;
ENAME SAL MAX_11 MAX_12
—————————— ———- ———- ———-
JAMES 950 1250 1250
WARD 1250 1600 1250
MARTIN 1250 1600 1500
TURNER 1500 1600 1600
ALLEN 1600 1600 2850
BLAKE 2850 2850 2850
6 rows selected.