按前面帖子,可以得到正确结果,--獲取結果
SQL> select col1,ltrim(MAX(sys_connect_by_path(col2,',')),',') col2
from
(
select col1,col2,MIN(col2) over(partition by col1) col2_min,
(row_number() over(order by col1,col2))+(dense_rank() over (order by col1)) NumID
from t
)
start with col2=col2_min connect by NumID-1=Prior NumID
group by col1;
--求得的結果
COL1 COL2
---------- -----------------------
002 vl1,vl2,vl3,vl4
001 vl1,vl2,vl3但是!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
使用800行记录进行测试,在一台不错的小型机上,结果要61秒,太慢了
select col1,col2,MIN(col2) over(partition by col1) col2_min,
(row_number() over(order by col1,col2))+(dense_rank() over (order by col1)) NumID
from t
结果建临时表,每个字段加个索引,仍无改进,请高高手指点
SQL> select col1,ltrim(MAX(sys_connect_by_path(col2,',')),',') col2
from
(
select col1,col2,MIN(col2) over(partition by col1) col2_min,
(row_number() over(order by col1,col2))+(dense_rank() over (order by col1)) NumID
from t
)
start with col2=col2_min connect by NumID-1=Prior NumID
group by col1;
--求得的結果
COL1 COL2
---------- -----------------------
002 vl1,vl2,vl3,vl4
001 vl1,vl2,vl3但是!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
使用800行记录进行测试,在一台不错的小型机上,结果要61秒,太慢了
select col1,col2,MIN(col2) over(partition by col1) col2_min,
(row_number() over(order by col1,col2))+(dense_rank() over (order by col1)) NumID
from t
结果建临时表,每个字段加个索引,仍无改进,请高高手指点
解决方案 »
- oracle 数据查询出现的问题
- 有多个端口级别判断线段级别问题(SQL)
- 裸体跪求各位大虾 COBOL 如何调ORACLE 存储过程
- 配置监听服务时出错,急!
- 请高手帮我看看oracle 10 rac实例启动出现问题 谢谢了!!!!!!
- 求各位高手帮忙啊!!!多谢大家!!!!高分相送!
- 动态sql怎么连接字符串,,
- oracle805上哪有developer 2000啊?买了一本书做实验找不到
- 如何列出当前用户下可见的所有表名?
- 请问oracle怎么样进入 enterprise manager
- 招聘软件开发,软件测试--应届毕业生
- TNS-12154 TNS:could not resolve service name
2 SELECT trunc(ROWNUM/50),'N' || to_char(ROWNUM),'V' || to_char(ROWNUM)
3 FROM dual CONNECT BY ROWNUM <= 9999
4 /9999 rows insertedExecuted in 0.06 secondsSQL> commit;Commit completeSQL> SELECT col1, ltrim(MAX(sys_connect_by_path(col2, ',')), ',') col2
2 FROM (SELECT col1,
3 col2,
4 MIN(col2) over(PARTITION BY col1) col2_min,
5 (row_number() over(ORDER BY col1, col2)) + (dense_rank() over(ORDER BY col1)) NumID
6 FROM test)
7 START WITH col2 = col2_min
8 CONNECT BY NumID - 1 = PRIOR NumID
9 GROUP BY col1; COL1 COL2
---------- --------------------------------------------------------------------------------
0 N1,N10,N11,N12,N13,N14,N15,N16,N17,N18,N19,N2,N20,N21,N22,N23,N24,N25,N26,N27,N2
1 N50,N51,N52,N53,N54,N55,N56,N57,N58,N59,N60,N61,N62,N63,N64,N65,N66,N67,N68,N69,
2 N100,N101,N102,N103,N104,N105,N106,N107,N108,N109,N110,N111,N112,N113,N114,N115,
3 N150,N151,N152,N153,N154,N155,N156,N157,N158,N159,N160,N161,N162,N163,N164,N165,
.
.
.
.
199 N9950,N9951,N9952,N9953,N9954,N9955,N9956,N9957,N9958,N9959,N9960,N9961,N9962,N9200 rows selectedExecuted in 1.172 seconds
又遇到一个问题,居然因为字数太多,出现
ora-01489:result of string concatenation is too long.怎么解决?