--有表TB,有两个字段,测试数据如下
CreateDay cnt2009-12-01 (A1-16)
2009-12-01 (A4-3)
2009-12-01 (B5-2)
2009-12-02 (A1-6)
2009-12-14 (A1-1)
2009-12-17 (A5-1)
2009-12-17 (B5-13)
2009-12-21 (A1-1)
2010-01-19 (A1-1)
2010-01-19 (B5-7)
2010-01-20 (A1-5)
2010-01-20 (B5-2)
2010-01-20 (B6-1)-----------------------------
-- 如何将其查询,使之生成结果如下:??CreateDay cnt2009-12-01 (A1-16)#(A4-3)#(B5-2)
2009-12-02 (A1-6)
2009-12-14 (A1-1)
2009-12-17 (A5-1)#(B5-13)
2009-12-21 (A1-1)
2010-01-19 (A1-1)#(B5-7)
2010-01-20 (A1-5)#(B5-2)#(B6-1)
CreateDay cnt2009-12-01 (A1-16)
2009-12-01 (A4-3)
2009-12-01 (B5-2)
2009-12-02 (A1-6)
2009-12-14 (A1-1)
2009-12-17 (A5-1)
2009-12-17 (B5-13)
2009-12-21 (A1-1)
2010-01-19 (A1-1)
2010-01-19 (B5-7)
2010-01-20 (A1-5)
2010-01-20 (B5-2)
2010-01-20 (B6-1)-----------------------------
-- 如何将其查询,使之生成结果如下:??CreateDay cnt2009-12-01 (A1-16)#(A4-3)#(B5-2)
2009-12-02 (A1-6)
2009-12-14 (A1-1)
2009-12-17 (A5-1)#(B5-13)
2009-12-21 (A1-1)
2010-01-19 (A1-1)#(B5-7)
2010-01-20 (A1-5)#(B5-2)#(B6-1)
select CreateDay,relpace(wm_concat(cnt),',','#') from tb;
select CreateDay,regexp_relpace(wm_concat(cnt),',','#') from tb;
create table tb(createtime varchar(20),cnt varchar(100));insert into tb(createtime,cnt)
values('2009-12-01','(A1-16)');
insert into tb(createtime,cnt)
values('2009-12-01','(A4-3)');
insert into tb(createtime,cnt)
values('2009-12-01','(B5-2)');
insert into tb(createtime,cnt)
values('2009-12-02','(A1-6)');
insert into tb(createtime,cnt)
values('2009-12-14','(A1-1)');
insert into tb(createtime,cnt)
values('2009-12-17','(A5-1)');
insert into tb(createtime,cnt)
values('2009-12-17','(B5-13)');
insert into tb(createtime,cnt)
values('2009-12-21','(A1-1)');
insert into tb(createtime,cnt)
values('2010-01-19','(B5-7)');
insert into tb(createtime,cnt)
values('2010-01-20','(A1-5)');
insert into tb(createtime,cnt)
values('2010-01-20','(B5-2)');
insert into tb(createtime,cnt)
values('2010-01-20','(B6-1)');
select Createtime,regexp_replace(wm_concat(cnt),',','#') as cnt from tb
group by createtime;
from tb t
group by t.createtime
group by createtime;
c1,c2两列,先用GROUP BY以后COUNT(×)来获取最大分组行数Select c1
max(decode(rn,1,c2,null)) c2_1,
max(decode(rn,2,c2,null)) c2_2,
…
max(decode(rn,N,c2,null)) c2_N
from (select c1, c2
row_number() over (partition by C1
order by <something>)
rn from T
<some predicate>)
group by C1
其原型是:regexp_replace(x,pattern[,replace_string[,start[,occurence[match_option]]]])
每个参数的意思分别是:
x 待匹配的函数
pattern 正则表达式元字符构成的匹配模式
replace_string 替换字符串
start 开始位置
occurence 匹配次数
match_option 匹配参数
--呵呵:真细心,但:cnt里面本身不会含有“,”字符