1

2

3

4

5

6

7

8

9

10

11

create table emp(

    emp_id int primary key auto_increment comment '编号',

    emp_name char(20) not null default '' comment '姓名',

    salary decimal(10,2) not null default 0 comment '工资',

    department char(20) not null default '' comment '部门'

);

insert into emp(emp_name,salary,department)

values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),

('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),

('刘云鹏',7800,'销售部');

1

group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])

1

select department,group_concat(emp_name) from emp group by department;

1

select department,group_concat(emp_name SEPARATOR ',') as e_name from emp group by department;

1

2

select department,group_concat(distinct emp_name)

from emp group by department;

1

2

select department,group_concat(distinct emp_name order by salary desc)

from emp group by department;

1

2

select department,group_concat(distinct emp_name order by salary desc separator '@')

from emp group by department;