因为业务需要对一些数据进行统计,需要对表涉及到的不同部门的进行过任务的用户id进行拼接之后到java代码中进行去重计数。
可是某一天,这段sql在运行的时候突然报错,提示“ 字符串连接的结果过长”,网上查询之后才知道,原来用listagg进行拼接,返回的结果是varchar类型,而且有最大长度限制,可以转成club或者截取。。。
开始准备使用WM_CONCAT,放进去之后发现提示没有这个函数。
查了一下原来是因为这个函数依赖WMSYS用户,这个版本并没有配置,无奈只能退而求其次选择了listagg函数。一轮下来效果不错,想要的效果可以实现。
SELECT
r.department_id law_dept_id,
(
SELECT
listagg (
rt.extracted_person_ids,
','
) WITHIN GROUP (
ORDER BY
rt.extracted_person_ids
)
FROM
RANDOM_TASK rt,
SYS_DEPARTMENT sd
WHERE
rt.CREAT_DEPARTMENT_ID = sd.DEPARTMENT_ID
AND sd.DEPARTMENT_NUMBER LIKE CONCAT (r.department_num, '%')
AND rt.distributed_state = 1
AND rt.STATE = 1
AND rt.LAUNCH_DATE BETWEEN TO_DATE ('2017-07-05', 'yyyy-mm-dd')
AND (TO_DATE('2017-07-05', 'yyyy-mm-dd') + 1)
) law_user_str
FROM
random_user_count r
ORDER BY
r.area_code,
r.department_num;
可是某一天,这段sql在运行的时候突然报错,提示“ 字符串连接的结果过长”,网上查询之后才知道,原来用listagg进行拼接,返回的结果是varchar类型,而且有最大长度限制,可以转成club或者截取。。。
后来根据各种链接,找到一个相对靠谱的回答,点击打开链接
改了之后的sql如下:
SELECT
r.department_id law_dept_id,
(
SELECT
-- listagg (
-- rt.extracted_person_ids,
-- ','
-- ) WITHIN GROUP (
-- ORDER BY
-- rt.extracted_person_ids
-- )
xmlagg(xmlparse(content rt.extracted_person_ids||',' wellformed) order by rt.extracted_person_ids).getclobval()
FROM
RANDOM_TASK rt,
SYS_DEPARTMENT sd
WHERE
rt.CREAT_DEPARTMENT_ID = sd.DEPARTMENT_ID
AND sd.DEPARTMENT_NUMBER LIKE CONCAT (r.department_num, '%')
AND rt.distributed_state = 1
AND rt.STATE = 1
AND rt.LAUNCH_DATE BETWEEN TO_DATE ('2017-07-05', 'yyyy-mm-dd')
AND (TO_DATE('2017-07-05', 'yyyy-mm-dd') + 1)
) law_user_str
FROM
random_user_count r
ORDER BY
r.area_code,
r.department_num;