oracle wm_concat(column)函数实现字段合并
例:
表
create table pm_ci
(ci_id varchar(20) primary key,
stu_ids varchar(100));
insert into pm_ci values('1','1,2,3,4');
insert into pm_ci values('2','1,4');
create table pm_stu
(stu_id varchar(20) primary key,
stu_name varchar(20));
insert into pm_stu values('1','张三');
insert into pm_stu values('2','李四');
insert into pm_stu values('3','王五');
insert into pm_stu values('4','赵六');
commit;
select a.ci_id,b.stu_name
from pm_ci a,pm_stu b
where instr(a.stu_ids,b.stu_id) > 0;
CI_ID | STU_NAME |
1 | 张三 |
1 | 李四 |
1 | 王五 |
1 | 赵六 |
2 | 张三 |
2 | 赵六 |
使用oracle wm_concat(column)函数实现:
select ci_id,wm_concat(stu_name) nameslist
from(select a.ci_id,b.stu_name
from pm_ci a,pm_stu b
where instr(a.stu_ids,b.stu_id) > 0)
group by ci_id;
CI_ID | NAMESLIST |
1 | 张三,赵六,王五,李四 |
2 | 张三,赵六 |