【 使用环境 】测试环境
【 OB or 其他组件 】 OB oracle
【 使用版本 】3.2.3.2
【问题描述】
如查询select name,label from xxtable;
丨name丨label丨
丨张三丨1丨
丨李四丨1丨
想要实现的效果:
丨name丨label丨
丨张三,李四丨1丨
通过oracle语句实现(考虑长度超过4000的情况):
select TO_CLOB(RTRIM(XMLAGG(XMLPARSE(CONTENT NAME || ‘,’ WELLFORMED)).getclobval(), ‘,’)
from xxtable
group by label;
丨name丨label丨
丨张三,李四丨1丨
mysql模式:
create table tb_user(
id bigint not null,
name varchar(32)
);
insert into tb_user values(1,‘张三’);
insert into tb_user values(1,‘李四’);
insert into tb_user values(1,‘张三’);
select id,group_concat(name) from tb_user group by id;
select id,group_concat(name order by name) from tb_user group by id;
select id,group_concat(distinct name) from tb_user group by id;
select id,group_concat(distinct name order by name) from tb_user group by id;
oracle模式:
create table tb_user(
id number not null,
name varchar(32)
);
insert into tb_user values(1,‘张三’);
insert into tb_user values(1,‘李四’);
insert into tb_user values(1,‘张三’);
select id,listagg(name,’,’) within group (order by name) from tb_user group by id;
select id,listagg(distinct name,’,’) within group (order by name) from tb_user group by id;