17,377
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE T_TYPE
(
T_TYPE_ID NUMBER,
T_TYPE_NAME VARCHAR2(20)
);
INSERT INTO T_TYPE
SELECT 1, '苹果' FROM DUAL UNION ALL
SELECT 2, '香蕉' FROM DUAL;
CREATE TABLE T_INFO
(
T_PERSON_ID NUMBER,
T_PERSON_NAME VARCHAR(20),
T_TYPE_ID VARCHAR(200)
);
INSERT INTO T_INFO
SELECT 1, '张三', '1,2' FROM DUAL UNION ALL
SELECT 1, '张三', '2' FROM DUAL UNION ALL
SELECT 2, '李四', '1' FROM DUAL
/*
结果--
把ID 转换成Name
1 1 张三 苹果,香蕉
2 1 张三 香蕉
3 2 李四 苹果
*/
select T_PERSON_ID,
T_PERSON_NAME,
(select LISTAGG(T_TYPE_NAME, ', ') WITHIN GROUP (ORDER BY T_TYPE_ID)
from T_TYPE
where T_TYPE_ID in
(SELECT regexp_substr(a.T_TYPE_ID, '[^,]+', 1, LEVEL)
FROM dual
CONNECT BY LEVEL < length(a.T_TYPE_ID)))
from T_INFO a;