ORACLE中表 test ( a,b,c)有记录如下:
A B C
1 XXX 01
2 YYY 01
3 KKK 02
4 III 02
。
我需要把C相同的B根据序号A串联起来,即得到这样的结果
B C
XXXYYY 01
KKKIII 02
。
这样的SQL怎么写?是不是ORACLE中无法解决这样的问题?请赐教,谢谢
A B C
1 XXX 01
2 YYY 01
3 KKK 02
4 III 02
。
我需要把C相同的B根据序号A串联起来,即得到这样的结果
B C
XXXYYY 01
KKKIII 02
。
这样的SQL怎么写?是不是ORACLE中无法解决这样的问题?请赐教,谢谢
create table test(a int,b varchar2(100),c varchar2(100));
insert into test
select 1,'XXX','01' from dual union all
select 2,'YYY','01' from dual union all
select 3,'KKK','02' from dual union all
select 4,'III','02' from dual;
--执行查询
select c,replace(substr(max(sys_connect_by_path(b, '|')), 2),'|','') b
from (select b, c, row_number() over(partition by c order by 1) rn
from test)
start with rn = 1
connect by rn - 1 = prior rn and c = prior c
group by c;
--查询结果
c b
01 XXXYYY
02 KKKIII
2 UNION
3 SELECT 2 A,'YYY' B,'01' C FROM DUAL
4 UNION
5 SELECT 3 A,'KKK' B,'02' C FROM DUAL
6 UNION
7 SELECT 4 A,'III' B,'02' C FROM DUAL
8 )
9 select C,MAX(SYS_CONNECT_BY_PATH(B,' ')) B FROM
10 (SELECT A,B,C,ROW_NUMBER()OVER(PARTITION BY C ORDER BY C) RN FROM A)
11 GROUP BY C
12 START WITH RN=1
13 CONNECT BY RN-1=PRIOR RN AND C=PRIOR C
14 ;
C B
-- --------------------------------------------------------------------------------
01 XXX YYY
02 KKK III