问题是这样:
SELECT
so.process_instance_id,
( SELECT LTRIM(
MAX(SYS_CONNECT_BY_PATH(wo, ',')), ',')
FROM (SELECT wo, RN, LEAD(RN) OVER(ORDER BY RN) RN1
FROM (SELECT wo,
ROW_NUMBER() OVER(ORDER BY wo DESC) RN
FROM (
SELECT
wi.workstep_name wo
FROM
workitem wi
WHERE
wi.status IN('I_ASSIGNED','I_AVAILABLE')
and wi.process_instance_id=so.process_instance_id
)
)
)
START WITH RN1 IS NULL
CONNECT BY RN1 = PRIOR RN
) test
FROM
serviceordercpemacd so,
processinstance pi
WHERE
so.process_instance_id=pi.process_instance_id
我的test想查到的结果是与serviceordercpemacd 相关的满足条件的所有workitem的名字,以‘,’连接成的字符串。但是我现在的sql是有问题的,错误提示是字符串的结果过长,这个是什么问题呢?或是谁可以给我一个可以使用的代码呢?
其中SELECT
wi.workstep_name wo
FROM
workitem wi
WHERE
wi.status IN('I_ASSIGNED','I_AVAILABLE')
and wi.process_instance_id=so.process_instance_id返回的结果可能只是一条或两条记录。
SELECT
so.process_instance_id,
( SELECT LTRIM(
MAX(SYS_CONNECT_BY_PATH(wo, ',')), ',')
FROM (SELECT wo, RN, LEAD(RN) OVER(ORDER BY RN) RN1
FROM (SELECT wo,
ROW_NUMBER() OVER(ORDER BY wo DESC) RN
FROM (
SELECT
wi.workstep_name wo
FROM
workitem wi
WHERE
wi.status IN('I_ASSIGNED','I_AVAILABLE')
and wi.process_instance_id=so.process_instance_id
)
)
)
START WITH RN1 IS NULL
CONNECT BY RN1 = PRIOR RN
) test
FROM
serviceordercpemacd so,
processinstance pi
WHERE
so.process_instance_id=pi.process_instance_id
我的test想查到的结果是与serviceordercpemacd 相关的满足条件的所有workitem的名字,以‘,’连接成的字符串。但是我现在的sql是有问题的,错误提示是字符串的结果过长,这个是什么问题呢?或是谁可以给我一个可以使用的代码呢?
其中SELECT
wi.workstep_name wo
FROM
workitem wi
WHERE
wi.status IN('I_ASSIGNED','I_AVAILABLE')
and wi.process_instance_id=so.process_instance_id返回的结果可能只是一条或两条记录。
1
2
3
workitem pid workstep_name
1 test1
1 test2
想要的结果 result pid workstep_name
1 test1,test2
SELECT '1' as pid from dual
union all
SELECT '2' as pid from dual
union all
SELECT '3' as pid from dual
),
workitem as (
SELECT '1' as pid,'test1' AS workstep_name from dual
union all
SELECT '1' as pid,'test2' AS workstep_name from dual
union all
SELECT '2' as pid,'test3' AS workstep_name from dual
union all
SELECT '2' as pid,'test4' AS workstep_name from dual
union all
SELECT '2' as pid,'test5' AS workstep_name from dual
union all
SELECT '3' as pid,'test6' AS workstep_name from dual
)select pid,listagg(wn,',') within group(order by pid) as workstep_name from(
select t1.pid,t2.workstep_name as wn from serviceordermace t1 left join workitem t2 on t1.pid = t2.pid) group by pid
-----------------------------
1 1 test1,test2
2 2 test3,test4,test5
3 3 test6
SELECT pid, max(str)
FROM (SELECT pid, sys_connect_by_path(wn, ',') AS str
FROM (SELECT pid,
wn,
rn,
lead(rn) over(PARTITION BY pid ORDER BY rn) rn1
FROM (SELECT pid, wn, row_number() over(ORDER BY pid) rn
FROM (select t1.pid, t2.workstep_name as wn
from serviceordermace t1
left join workitem t2 on t1.pid = t2.pid)))
CONNECT BY rn1 = PRIOR rn)
group by pid
order by pid
写的太冲忙,自己调整下。
FROM (
SELECT serviceordermace.pid,
workitem.workstep_name,
ROW_NUMBER() OVER(PARTITION BY serviceordermace.pid ORDER BY workitem.workstep_name) GrepSeq
FROM serviceordermace ,workitem
WHERE workitem.pid=serviceordermace.pid
)
START WITH GrepSeq=1
CONNECT BY PRIOR GrepSeq=GrepSeq-1
AND pid = PRIOR pid
GROUP BY pid
ORDER BY 1