oracle keep(dense_rank ) over()

举例，根据数据来分析keep(dense_rank ) over() 的用法，与查询结果。

如下：

CREATETABLE wty_testAS

SELECT1ID,111 mc,1 sl FROM dual

UNIONALL

SELECT1ID,222 mc,1 sl FROM dual

UNIONALL

SELECT1ID,333 mc,2 sl FROM dual

UNIONALL

SELECT1ID,555 mc,3 sl FROM dual

UNIONALL

SELECT1ID,666 mc,3 sl FROM dual

UNIONALL

SELECT2ID,111 mc,1 sl FROM dual

UNIONALL

SELECT2ID,222 mc,1 sl FROM dual

UNIONALL

SELECT2ID,333 mc,2 sl FROM dual

UNIONALL

SELECT2ID,555 mc,2 sl FROM dual;

 

 

SELECT *FROM wty_test;

 

SELECT

      ID,

      mc,

      sl,

      MIN(mc)KEEP(DENSE_RANKFIRSTORDERBY sl)OVER(PARTITIONBYID) min_first_mc,

      max(mc)KEEP(DENSE_RANKFIRSTORDERBY sl)OVER(PARTITIONBYID) max_first_mc,

      MIN(mc)KEEP(DENSE_RANKLASTORDERBY sl)OVER(PARTITIONBYID) min_last_mc,

      max(mc)KEEP(DENSE_RANKLASTORDERBY sl)OVER(PARTITIONBYID) max_last_mc

 FROM wty_test;