Oracle SQL进行连续分组

本文介绍了Oracle SQL进行连续分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要从具有以下结构和数据的表中生成报告.

I need to generate a report from a table with the structure and data as given below.

Table Ticket具有下面给出的数据.

Table Ticket has data as given below.

ID Assigned_To 100 raju 101 raju 102 raju 103 anil 104 anil 105 sam 106 raju 107 raju 108 anil

Oracle SELECT应该生成以下报告

The Oracle SELECT should generate the below report

From_Id To_Id Assigned_To 100 102 raju 103 104 anil 105 105 sam 106 107 raju 108 108 anil

有人可以帮我建立一个查询吗?.

Can someone please help me with building a query..?

预先感谢， 马修.

推荐答案

SQL> create table ticket (id,assigned_to) 2 as 3 select 100, 'raju' from dual union all 4 select 101, 'raju' from dual union all 5 select 102, 'raju' from dual union all 6 select 103, 'anil' from dual union all 7 select 104, 'anil' from dual union all 8 select 105, 'sam' from dual union all 9 select 106, 'raju' from dual union all 10 select 107, 'raju' from dual union all 11 select 108, 'anil' from dual 12 / Tabel is aangemaakt. SQL> select min(id) from_id 2 , max(id) to_id 3 , assigned_to 4 from ( select id 5 , assigned_to 6 , id - row_number() over (partition by assigned_to order by id) grp 7 from ticket 8 ) 9 group by assigned_to 10 , grp 11 order by from_id 12 / FROM_ID TO_ID ASSIGNED_TO ---------- ---------- ----------- 100 102 raju 103 104 anil 105 105 sam 106 107 raju 108 108 anil 5 rijen zijn geselecteerd.

**更新与tuinstoel解决方案的性能比较的结果:

**UPDATE with the results of a performance comparison with tuinstoel's solution:

在11.1.0.7上:

On 11.1.0.7:

SQL> exec runstats_pkg.rs_start PL/SQL procedure successfully completed. SQL> set termout off SQL> select min(id) from_id 2 , max(id) to_id 3 , assigned_to 4 from ( select id 5 , assigned_to 6 , id - row_number() over (partition by assigned_to order by id) grp 7 from ticket 8 ) 9 group by assigned_to 10 , grp 11 order by from_id 12 / FROM_ID TO_ID ASSI ---------- ---------- ---- 100 102 raju 103 104 anil 105 105 sam 106 107 raju 108 108 anil 109 111 raju <snip> 589921 589922 raju 589923 589923 anil 327680 rows selected. SQL> set termout on SQL> exec runstats_pkg.rs_middle PL/SQL procedure successfully completed. SQL> set termout off SQL> select * from table(testpl.pltest) 2 / FROM_ID TO_ID ASSI ---------- ---------- ---- 100 102 raju 103 104 anil 105 105 sam 106 107 raju 108 108 anil 109 111 raju <snip> 589921 589922 raju 589923 589923 anil 327680 rows selected. SQL> set termout on

结果:

SQL> exec runstats_pkg.rs_stop(100) Run1 draaide in 547 hsecs Run2 draaide in 549 hsecs Run1 draaide in 99.64% van de tijd Naam Run1 Run2 Verschil STAT.recursive cpu usage 2 106 104 LATCH.row cache objects 91 217 126 STAT.bytes received via SQL*Net from client 37,496 37,256 -240 STAT.recursive calls 7 5,914 5,907 STAT.table scan rows gotten 615,235 589,824 -25,411 STAT.sorts (rows) 917,504 589,824 -327,680 Run1 latches totaal versus run2 -- verschil en percentage Run1 Run2 Verschil Pct 10,255 10,471 216 97.94% PL/SQL procedure successfully completed.

关于， 罗布.