创建表
-- 合并行 group_concat(list, separeator ',')
drop table if exists table1;
create table table1(
company_name varchar(20) not null,
person_name varchar(20) not null,
person_position varchar(20) not null
);
insert into table1(company_name, person_name, person_position) values ('bonc','chen','manager');
insert into table1(company_name, person_name, person_position) values ('bonc','yu','manager');
insert into table1(company_name, person_name, person_position) values ('baidu','sun','inv');
insert into table1(company_name, person_name, person_position) values ('baidu','zhou','manager');
图示:
合并行
select t.company_name,
group_concat(t.name_position separator '||') as name_position
from ( -- 合并字段列 concat
select company_name, concat(person_name,'@',person_position) as name_position
from table1
) t
group by company_name;
图示: