问题:
还是行列转换问题,不定转换
请问我有一个表a,有字段a1,a2,a3,a4,a5
我现在得到如下查询结果咋办?
把所有a1和a2字段相同的做为一条记录,然后把a3,a4,a5连接起来做为查询结果
例如
a1 a2 a3 a4 a5
1 1 a b c
1 1 d e f
1 2 a b c
1 2 h i g
1 2 c c c
要得到
1 1 abcdef
1 2 abchigccc
说明a1和a2的内容是不定的,a1和a2的行数也是不确定的。在大数据量的情况下采取哪种方式较好?
回复:
参考下面的
SQL> select * from emp;
A1 A2 A3 A4 A5
-- -- -- -- --
1 1 A B C
1 1 D E F
1 2 A B C
1 2 H I G
1 2 C D E
SQL>
SQL> Select t.a1,t.a2,replace(Max(sys_connect_by_path(t.aa,',')),',',Null)
2 From
3 (
4 Select t1.a1,
5 t1.a2,
6 decode(row_number() over (Partition By a1,a2,a3,a4,a5 Order By seq),1,A3,2,A4,3,A5) As aa,
7 row_number() over (Partition By a1,a2 Order By a1,a2) As seq
8 From emp t1,(Select Level As seq From dual Connect By Rownum<=3 ) t2
9 ) t
10 Start With seq=1
11 Connect By seq-1=Prior seq And a1=Prior a1 And a2=Prior a2
12 Group By t.a1,t.a2
13 ;
A1 A2 REPLACE(MAX(SYS_CONNECT_BY_PAT
-- -- --------------------------------------------------------------------------------
1 1 ABCDEF
1 2 ABCDEFHIG
阅读(949) | 评论(0) | 转发(0) |