本文介绍了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 anilOracle 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.关于, 罗布.
更多推荐
Oracle SQL进行连续分组
发布评论