项目中用到一个mailto有多个body（正文）时，要把body合并：
最初是这么做的
INSERT INTO TTA1M900(
MAILSEQ
,MAILFROM
,MAILTO
,MAILSUBJECT
,MAILBODY
,SSHNNCHJ
,SSHNJKY
,HTSGYCNT
,DELFLG
,LGNTRKSHSHNCD
,CRRNTTRKSHSHNCD
,TRKYMDS
,LGNKOSHNSHSHNCD
,CRRNTKOSHNSHSHNCD
,KOSHNYMDS
)
SELECT
W_MailSeq + ROWNUM
,a.MAILFROM
,a.MAILTO
,a.MAILSUBJECT
,a.MAILBODY
,SYSDATE
,'0'
,0
,'0'
,W_UserId ,W_UserId ,SYSDATE ,W_UserId ,W_UserId ,SYSDATE FROM (SELECT DISTINCT
t.MAILFROM,
t.MAILTO,
t.MAILSUBJECT,
CONCAT(CONCAT(CONCAT(t.MAILBODYHEAD, replace(tmp.MAILBODY2, ' ', '')), t.MAILBODYFOOT), t.MAILBODYOTHER) MAILBODY,
SYSDATE,
'0'
FROM (SELECT t2.MAILTO, LTRIM(MAX(SYS_CONNECT_BY_PATH(t2.MAILBODY2, ' ')), ' ') as MAILBODY2
FROM (SELECT t1.MAILTO, t1.MAILBODY2, rnfirst, lead(rnfirst) over(partition by t1.MAILTO order by rnfirst) rnnext
FROM (SELECT t0.MAILTO, t0.MAILBODY2, row_number() over(order by t0.MAILTO, t0.MAILBODY2 desc) rnfirst
FROM TTA1M904 t0
) t1
) t2
start with rnnext is null
connect by rnnext = prior rnfirst group by t2.MAILTO) tmp,
TTA1M904 t
WHERE tmp.MAILTO = t.MAILTO) a;
对于sys_connect_by_path当字符数大于4000，就彻底的完蛋了。解决的方案很不彻底，比如用cast to clob等。很不可靠。
robust是通不过的。
11gr2的方法listagg很好：
select * from stuscore
userid course scode
1 zhangsan chinese 76
2 zhangsan maths 81
3 zhangsan english 65
4 lisi chinese 81
5 lisi maths 82
6 lisi english 85
7 wangwu maths 90
select distinct userid, listagg(course, ',') within group (order by course desc) over(partition by userid) res1
from (select userid, course from stuscore order by userid)
userid res1
1 lisi maths,english,chinese
2 wangwu maths
3 zhangsan maths,english,chinese
纵列变横列,很奇妙吧. OK完成.
http://hiyachen.blog.chinaunix.net
关键在是否能用substrb
select distinct userid, substrb(listagg(course, ',') within group (order by course desc) over(partition by userid), 1, 10) res1
from (select userid, course from stuscore order by userid)
userid res1
1 zhangsan maths,engl
2 lisi maths,engl
3 wangwu maths
阅读(5672) | 评论(0) | 转发(0) |
