17,377
社区成员
发帖
与我相关
我的任务
分享
WITH t1 AS(
SELECT 'zhang san' SNAME,'00010204' elective FROM dual
UNION ALL
SELECT 'li si','010203' FROM dual
),t2 AS (
SELECT 'elective' Value,'00' id,'shu xue' cname FROM dual
UNION ALL
SELECT 'elective','01','xu wen' FROM dual
UNION ALL
SELECT 'elective','02','ying yu' FROM dual
UNION ALL
SELECT 'elective','03','C yu yan' FROM dual
UNION ALL
SELECT 'elective','04','Oracle' FROM dual
UNION ALL
SELECT 'elective','10','JAVA' FROM dual
)
select sname,wm_concat(cname) from
(
select a.sname,b.id,b.cname cname from t1 a left join t2 b on
a.elective like '%'||b.id||'%' AND Mod(InStr(a.elective,b.id)-1,2)=0
)group by sname;
没环境没调试,可以试试以下指令:
大致思路:
先换成这种形式
张三 00 数学
张三 01 语文
张三 02 英语
张三 04 Oracle
再以人名分组,利用wm_concat()函数
select sname,wm_concat(cname) from
(select a.name sname,b.id,b.name cname form tableA a left join tableB b on
a.elective like '%'||b.id||'%'
)group by sname;