【ORACLE/SQLSERVER/MySQL】字段聚合需求实现stuff，wm_concat，group_concat

【ORACLE DEMO】

create table testbzm(
id int,
note varchar(20)
);

insert into testbzm values(1,'xiaobai');
insert into testbzm values(1,'xiaohuan');
insert into testbzm values(1,'xiaohei');
insert into testbzm values(2,'111');
insert into testbzm values(2,'222');
insert into testbzm values(2,'333');

commit;

select id,note from testbzm;

select id,wmsys.wm_concat(note) gather from testbzm group by id;

【MYSQL DEMO】

create table testbzm(
id int,
note varchar(20)
);

insert into testbzm values(1,'xiaobai');
insert into testbzm values(1,'xiaohuan');
insert into testbzm values(1,'xiaohei');
insert into testbzm values(2,'111');
insert into testbzm values(2,'222');
insert into testbzm values(2,'333');

commit;

select id,note from testbzm;

select id,group_concat(note) gather from testbzm group by id;

【SQLSERVER DEMO】

create table testbzm(
id int,
note varchar(20)
);

insert into testbzm values(1,'xiaobai');
insert into testbzm values(1,'xiaohuan');
insert into testbzm values(1,'xiaohei');
insert into testbzm values(2,'111');
insert into testbzm values(2,'222');
insert into testbzm values(2,'333');


select id,note from testbzm;

SELECT id,
[note]=
stuff((select ','+[note] from testbzm t where t.id=a.id 
for xml path('')), 1, 1, '') 
FROM testbzm a
group by id