17,086
社区成员
发帖
与我相关
我的任务
分享
function f(s0 varchar2) is
s1 varchar2(128);
begin
s1 := ','||s0||','; --',01,02,03,'; 加逗号保证分隔
for c in select * from B where s1 like '%,'||vertype||',%'
loop
--这么麻烦的替换是为了保证顺序,否则按照楼上的WM_CONCAT就直接出来了。
s1 := replace(s1,','||c.vertype||',' , ','||c.vervalue||',');
end loop;
s1 := substr(s1, 2, length(s1)-2); --去除两端逗号
return s1;
end f;
select vertype, f(vertype) from A;
WITH a AS(
SELECT '' AS vertype FROM dual UNION
SELECT '01,' AS vertype FROM dual UNION
SELECT '01,02' AS vertype FROM dual UNION
SELECT '01,02,03' AS vertype FROM dual UNION
SELECT '01,03' AS vertype FROM dual
),b AS(
SELECT '01' AS vertype,'手机' AS vervalue FROM dual UNION
SELECT '02' AS vertype,'电话' AS vervalue FROM dual UNION
SELECT '03' AS vertype,'电脑' AS vervalue FROM dual
)
SELECT VERTYPE,D01||D02||D03 FROM
( SELECT A.VERTYPE,
CASE WHEN instr(vertype,'01')>0 THEN '手机' END AS D01,
CASE WHEN instr(vertype,'02')>0 THEN '电话' END AS D02,
CASE WHEN instr(vertype,'03')>0 THEN '电脑' END AS D03
FROM a
);
01, 手机
01,02 手机电话
01,02,03 手机电话电脑
01,03 手机电脑
WITH a AS(
SELECT '01,' AS vertype FROM dual UNION
SELECT '01,02' AS vertype FROM dual UNION
SELECT '01,02,03' AS vertype FROM dual UNION
SELECT '01,03' AS vertype FROM dual
),b AS(
SELECT '01' AS vertype,'手机' AS vervalue FROM dual UNION
SELECT '02' AS vertype,'电话' AS vervalue FROM dual UNION
SELECT '03' AS vertype,'电脑' AS vervalue FROM dual
)
SELECT A.VERTYPE, WMSYS.WM_CONCAT(VERVALUE) AS RESULT
FROM A, B
WHERE INSTR(A.VERTYPE, B.VERTYPE) > 0
GROUP BY A.VERTYPE;
结果:
01, 手机
01,02 手机,电话
01,02,03 手机,电脑,电话
01,03 手机,电脑
s1 := ','||'01,02,03'||',';
for c in select * from B where s1 like '%,'||vertype||',%'
loop
s1 := replace(s1,','||c.vertype||',' , ','||c.vervalue||',');
end loop;
s1 := substr(s1, 2, length(s1)-2);