现有一张表t1:
结构数据大致如下:
c1, c2, c3
1, A, a
1, A, b
1, B, c
2, A, d
2, A, e我想得到如下结果:1, A, ab
1, B, c
2, A, de不要用 wmsys.wm_concat, 最好一条SELECT 搞定。麻烦哪位大神给个答案!!
谢谢!~
结构数据大致如下:
c1, c2, c3
1, A, a
1, A, b
1, B, c
2, A, d
2, A, e我想得到如下结果:1, A, ab
1, B, c
2, A, de不要用 wmsys.wm_concat, 最好一条SELECT 搞定。麻烦哪位大神给个答案!!
谢谢!~
我用这个函数后,以此为基表,再操作的时候报错了, 查了下 说是这个函数 针对不同的数据库的版本,返回的数据类型不同,而且oracle 官方以后也许会修改这个函数什么的, 而且声明不建议使用这个函数, 出了错 他们不负责。
(select 1 c1,'A' c2,'a' c3 from dual union all
select 1 c1,'A' c2,'b' c3 from dual union all
select 1 c1,'B' c2,'c' c3 from dual union all
select 2 c1,'A' c2,'d' c3 from dual union all
select 2 c1,'A' c2,'e' c3 from dual
)
select c1,c2,replace(max(sys_connect_by_path(c3,',')),',','')
from (select a.*,row_number() over(partition by c1,c2 order by c3) rn from a) t
start with t.rn=1
connect by prior c1= c1 and prior c2= c2 and prior rn= rn-1
group by c1,c2
;
目测是有用的。。 我用sys_connect_by_path试了 很久 差别在 prior rn= rn-1
我是prior rn= rn-1 谢谢你 我明天去公司环境试下看看。
2 (
3 select 1 as c1,'A' as c2,'a' as c3 from dual union all
4 select 1,'A','b' from dual union all
5 select 1,'B','c' from dual union all
6 select 2,'A','d' from dual union all
7 select 2,'A','e' from dual
8 )
9 select distinct c1,c2,
10 listagg(c3)within group(order by c1,c2)over(partition by c1,c2) c3
11 from t group by c1,c2,c3 order by c1,c2; C1 C2 C3
---------- --- -----
1 A ab
1 B c
2 A edSQL>