oracle keep(dense_rank ) over()

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

如下：

CREATETABLE wty_test AS

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_RANK FIRSTORDERBY sl) OVER(PARTITIONBYID) min_first_mc,

      max(mc)KEEP(DENSE_RANK FIRSTORDERBY sl) OVER(PARTITIONBYID) max_first_mc,

      MIN(mc)KEEP(DENSE_RANK LASTORDERBY sl) OVER(PARTITIONBYID) min_last_mc,

      max(mc)KEEP(DENSE_RANK LASTORDERBY sl) OVER(PARTITIONBYID) max_last_mc

 FROM wty_test;