利用分析函数在Oracle中分组排序取首条或末条记录行

saillee 发表于 2011-12-22 08:54

在工作中，我们经常会使用到按时间或指定字段进行排序，并提取首条/末条记录的操作，而Oracle中的分析函数让我们能够方便快捷地实现这个功能，下面实例的详细情况：<div> </div><div>1、建立测试数据表</div><div> </div><div><div id="codeText" class="codeText"><ol start="1" class="dp-css"><li>create table sail_test </li><li>
(group_id number(3), </li><li>
 order_id number(3), </li><li>
 other1 varchar2(8), </li><li>
 other2 varchar2(8));</li></ol></div></div><div>2、插入测试数据</div><div> </div><div><div id="codeText" class="codeText"><ol start="1" class="dp-css"><li>insert into sail_test values (1, 1, 'g1_o11', 'g1_o21'); </li><li>
insert into sail_test values (1, 2, 'g1_o12', 'g1_o22'); </li><li>
insert into sail_test values (1, 3, 'g1_o13', 'g1_o23'); </li><li>
insert into sail_test values (1, 4, 'g1_o14', 'g1_o24'); </li><li>
insert into sail_test values (2, 1, 'g2_o11', 'g2_o21'); </li><li>
insert into sail_test values (2, 2, 'g2_o12', 'g2_o22'); </li><li>
insert into sail_test values (2, 3, 'g2_o13', 'g2_o23'); </li><li>
insert into sail_test values (3, 1, 'g3_o11', 'g3_o21'); </li><li>
insert into sail_test values (3, 2, 'g3_o12', 'g3_o22'); </li><li>
insert into sail_test values (3, 3, 'g3_o13', 'g3_o23');</li></ol></div></div><div>其中，group_id是分组的字段，order_id是排序字段，下面是所有数据的快照：</div><div> </div><div><div id="codeText" class="codeText"><ol start="1" class="dp-css"><li>GROUP_ID ORDER_ID OTHER1 OTHER2 </li><li>
-------- -------- -------- -------- </li><li>
       1 1 g1_o11 g1_o21 </li><li>
       1 2 g1_o12 g1_o22 </li><li>
       1 3 g1_o13 g1_o23 </li><li>
       1 4 g1_o14 g1_o24 </li><li>
       2 1 g2_o11 g2_o21 </li><li>
       2 2 g2_o12 g2_o22 </li><li>
       2 3 g2_o13 g2_o23 </li><li>
       3 1 g3_o11 g3_o21 </li><li>
       3 2 g3_o12 g3_o22 </li><li>
       3 3 g3_o13 g3_o23</li></ol></div></div><div>3、我们现在想查询出各个group_id中，order_id最小的那个记录行，下面是SQL：</div><div> </div><div><div id="codeText" class="codeText"><ol start="1" class="dp-css"><li>-- 以group_id分组，按照order_id排序（可以是倒序，order by语句后面加desc，取首条记录 </li><li>
select distinct a.group_id, </li><li>
       first_value(a.other1) over (partition by a.group_id order by order_id), </li><li>
       first_value(a.other2) over (partition by a.group_id order by order_id) </li><li>
from sail_test a </li><li>
order by a.group_id;</li></ol></div></div><div>查询结果为：</div><div> </div><div><div id="codeText" class="codeText"><ol start="1" class="dp-css"><li>GROUP_ID FIRST_VALUE(A.OTHER1)OVER(PART FIRST_VALUE(A.OTHER2)OVER(PART </li><li>
-------- ------------------------------ ------------------------------ </li><li>
       1 g1_o11 g1_o21 </li><li>
       2 g2_o11 g2_o21 </li><li>
       3 g3_o11 g3_o21</li></ol></div></div><div>4、总结</div><div> </div><div>Oracle中的分析函数使用很方便，在本例子中通过over来指定分组字段和排序字段的范围，通过first_value或last_value函数来获取首条或末条记录字段值，排序可以是顺序或倒序，而distinct则指定获取一条记录行。</div><div> </div>

页: [1]

Chinaunix's Archiver

利用分析函数在Oracle中分组排序取首条或末条记录行