oracle使用wm_concat进行分组拼接

例子:

SELECT a.period_id,wm_concat( b.activity_id) ACTIVITY_ID,wm_concat(a.lottery_number) LOTTERY_NUMBER,wm_concat(b.period_number) PERIOD_NUMBER,wm_concat(b.total_amount) COUNT,wm_concat(REPLACE(MOBILE, SUBSTR(a.mobile,4,4), &＃39;****&＃39;)) MOBILE,wm_concat(TO_CHAR(b.open_time,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;) ) OPEN_TIME
FROM A5_PPS_SEIZE_LOTTERY a ,a5_pps_seize_activity_period b
WHERE a.period_id&＃61;b.period_id

那为什么我还wm_concat 那么多别的字段了&＃xff1f;因为出来要返回period_id&＃xff0c;mobile外&＃xff0c;其他字段也需要返回&＃xff0c;所有虽然拼接多了很多字段&＃xff0c;但是有些自读是一样的&＃xff0c;比如 ACTIVITY_ID等&＃xff0c;这些自读返回后 用“,”切割成数组取出第一个就好了。

刚开始 使用ibatis映射时老是报错

Cause: com.ibatis.common.beans.ProbeException: There is no WRITEABLE property named &＃39;ACTIVITY_ID)&＃39; in class &＃39;java.lang.Object&＃39;

原始简化代码如下:

SELECT *
FROM(SELECT rownum rn,d.*FROM(SELECT a.period_id,wm_concat( b.activity_id)FROM A5_PPS_SEIZE_LOTTERY a ,a5_pps_seize_activity_period bWHERE a.period_id&＃61;b.period_idAND a.is_win     &＃61;1GROUP BY a.period_idORDER BY a.period_id DESC)d)
WHERE rn > 0
AND rn  <&＃61; 10 

发来发现  wm_concat( b.activity_id)  后面要取个别名&＃xff0c;才会不报上面那个映射错误&＃xff0c;正确写法如下

SELECT *
FROM(SELECT rownum rn,d.*FROM(SELECT a.period_id,wm_concat( b.activity_id) activity_idFROM A5_PPS_SEIZE_LOTTERY a ,a5_pps_seize_activity_period bWHERE a.period_id&＃61;b.period_idAND a.is_win &＃61;1GROUP BY a.period_idORDER BY a.period_id DESC)d)
WHERE rn > 0
AND rn <&＃61; 10