create table group_test(id int,sub_id int,v_date date);
insert into group_test values (1, 3, sysdate - 3);
insert into group_test values (1, 2, sysdate - 4);
insert into group_test values (2, 4, sysdate - 6);
insert into group_test values (2, 8, sysdate - 2);
commit;
select * from group_test;
select t2.id, sub_id, t2.v_date
from (select id, min(v_date) v_date from group_test group by id) t1,
group_test t2
where t1.id = t2.id
and t1.v_date = t2.v_date;
就是要找以ID分组每组的最小V_DATE值对应的sub_id的值。通常的简单方法我们是通过上面的子查询，那是否还有其他的不用子查询一条SQL可以完成的呢？答案是肯定的。这也就是偶今天要记录的东西（ORACLE的分析函数）就按照上面的需求我们还可以通过：
SQL> select * from group_test;
ID SUB_ID V_DATE
---------- ---------- -----------
1 3 2010-11-28
1 2 2010-11-27
2 4 2010-11-25
2 8 2010-11-29
--（1）
SQL> select id,
2 sub_id,
3 v_date
4 from
5 (select id,
6 sub_id,
7 v_date,
8 dense_rank() over(partition by id order by v_date) rn
9 from group_test)
10 where rn = 1;
ID SUB_ID V_DATE
---------- ---------- -----------
1 2 2010-11-27
2 4 2010-11-25
--（2）
SQL> select id,
2 min(sub_id) keep(dense_rank first order by v_date) sub_id,
3 min(v_date) v_date
4 from group_test
5 group by id;
ID SUB_ID V_DATE
---------- ---------- -----------
1 2 2010-11-27
2 4 2010-11-25
第二种写法的first,last参数ORACLE的手册上的解释是：
DENSE_RANK FIRST or DENSE_RANK LAST indicates that Oracle Database will aggregate over only those rows with the minimum (FIRST：最小值) or the maximum(LAST：最大值) dense rank (also called olympic rank).
|
