17,377
社区成员
发帖
与我相关
我的任务
分享
SELECT CASE_NO, ltrim(MAX(sys_connect_by_path(trim(ANKEN_NO), ',')), ',') userid
FROM
(SELECT CASE_NO, ANKEN_NO, row_number() over(PARTITION BY CASE_NO ORDER BY CASE_NO) rn FROM a)
START WITH rn = 1 CONNECT BY rn - 1 = PRIOR rn AND CASE_NO = PRIOR CASE_NO
GROUP BY CASE_NO ORDER BY CASE_NO;
select M_CASE,replace(max(sys_connect_by_path(ANKEN_NO , ', ')), ', ', ' ')
from
(SELECT
M_CASE
,ANKEN_NO
,MIN(ANKEN_NO) over(PARTITION BY M_CASE) minnm
,(row_number() over(ORDER BY M_CASE, ANKEN_NO))+(dense_rank() over(ORDER BY M_CASE)) no
FROM
TBL_MITUMORI_CASE2
)
start with M_CASE = minnm
connect by no-1 = prior no
group by M_CASE
with x AS (
SELECT '张三' NAME, 'KB101' userid FROM dual UNION ALL
SELECT '张三', 'KB002' FROM dual UNION ALL
SELECT '张三', 'KB113' FROM dual UNION ALL
SELECT '张三', 'KB003' FROM dual UNION ALL
SELECT '张三', 'KB004' FROM dual UNION ALL
SELECT '张三', 'KB005' FROM dual UNION ALL
SELECT '张三', 'KB013' FROM dual UNION ALL
SELECT '张三', 'KB103' FROM dual UNION ALL
SELECT '李四', 'KB001' FROM dual UNION ALL
SELECT '李四', 'KB002' FROM dual UNION ALL
SELECT '王五', 'KB002' FROM dual UNION ALL
SELECT '王五', 'KB006' FROM dual UNION ALL
SELECT '李四', 'KB003' FROM dual)
--//--直接提取法
SELECT NAME, ltrim(MAX(sys_connect_by_path(userid, ',')), ',') userid
FROM (SELECT NAME, userid, row_number() over(PARTITION BY NAME ORDER BY userid) rn FROM x)
START WITH rn = 1 CONNECT BY rn - 1 = PRIOR rn AND NAME = PRIOR NAME GROUP BY NAME ORDER BY NAME;