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.