对一个sql的理解

select id,max(num),max(flag)keep(dense_rank last order by num) from table1

group by id

Oracle从8i开始就提供了3个分析函数：rank，dense_rank，row_number

Rank，Dense_rank，Row_number函数为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。

 

①ROW_NUMBER：

Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。

 

②DENSE_RANK：

Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。

 

③RANK：

Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

 

keep是Oracle下的另一个分析函数，他的用法不同于通过over关键字指定的分析函数，可以用于这样一种场合下：取同一个分组下以某个字段排序后，对指定字段取最小或最大的那个值。

从这个前提出发，我们可以看到其实这个目标通过一般的row_number分析函数也可以实现，即指定rn=1。但是，该函数无法实现同时获取最大和最小值。或者说用first_value和last_value，结合row_number实现，但是该种方式需要多次使用分析函数，而且还需要套一层SQL。于是出现了keep，该函数先可以参考如下链接：

 

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions056.htm#SQLRF00641

 

Id这个分组下，同过num字段排序后，

 

SQL> select id,mc,sl,

2 min(mc) keep (DENSE_RANK first ORDER BYsl) over(partition by id),

3 max(mc) keep (DENSE_RANK last ORDER BYsl) over(partition by id)

4 from test

5 ;

 

ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORDMAX(MC)KEEP(DENSE_RANKLASTORDE

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

1 111 1 111 666

1 222 1 111 666

1 333 2 111 666

1 555 3 111 666

1 666 3 111 666

2 111 1 111 555

2 222 1 111 555

2 333 2 111 555

2 555 2 111 555

 

min(mc) keep (DENSE_RANK first ORDER BY sl)over(partition by id)：id等于1的数量最小的（DENSE_RANK first ）为

1 111 1

1 222 1

在这个结果中取min(mc) 就是111

max(mc) keep (DENSE_RANK first ORDER BY sl)over(partition by id)

取max(mc) 就是222；

min(mc) keep (DENSE_RANK last ORDER BY sl)over(partition by id)：id等于1的数量最大的（DENSE_RANK first ）为

1 555 3

1 666 3

在这个结果中取min(mc) 就是222，取max(mc)就是666