(将相同列整合成一列)
实例:对相同id进行分组,并合并OBJ。
ID | OBJ |
---|---|
1 | 国文 |
2 | 历史 |
3 | 生物 |
1 | 美术 |
3 | 音乐 |
3 | 体育 |
2 | 美术 |
2 | 英文 |
1 | 数学 |
4 | 化学 |
1 | 物理 |
结果:如下所示,显示不同id的学生选择的课程,课程直接以’,'分割
ID | OBJ_ALL |
---|---|
1 | 国文,美术,数学,物理 |
2 | 历史,美术,英文 |
3 | 生物,体育,音乐 |
4 | 化学 |
实现方式:
1、Oracle(关键字:listagg within group (order by )
、group by id
)
select id,listagg(obj,',') within group (order by id) as obj_all
from student group by id;
2、MySQL(关键字:group_concat
、group by
)
select id,group_concat(obj) as obj_all
from student group by id;
建表语句:
create table student(
id number(2),
obj varchar2(16)
);
insert into student values(1,'国文');
insert into student values(2,'历史');
insert into student values(3,'生物');
insert into student values(1,'美术');
insert into student values(3,'音乐');
insert into student values(3,'体育');
insert into student values(2,'美术');
insert into student values(2,'英文');
insert into student values(1,'数学');
insert into student values(4,'化学');
insert into student values(1,'物理');
commit;