函数
group_concat
数据准备
create database mydb4;
use mydb4;
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,'销售部');
part1
-- 将所有员工的名字和成一行
select group_concat(emp_name) from emp;
运行结果如下
part2
-- 指定分割符
select group_concat(emp_name separator ';') from emp;
运行结果如下
part 3
-- 分组后拼接
select department,group_concat(emp_name separator ';') from emp group by department;
part 4 按照薪资排序
-- 分组后拼接,并排序
select department,group_concat(emp_name order by salary desc separator ';') from emp group by department;