mysql基础知识理解和sql题讲解分析面试实战（三）之group

2019独角兽企业重金招聘Python工程师标准>>>

####mysql基础知识理解和sql题讲解分析面试实战（一）之group_concat

group_concat 将同一字段的多行内容以一定的函数合成一行内容

####用例 sql 语句如下该用例主要考察group_concat


# 如题：用sql查询jimmy 的下属，并将下属的名字拼接在一个字段输出，中间用“，”隔开
#创建department 和employee表

create table department(
id int(10) not null,
departmentName VARCHAR(20),
leaderName varchar(20),
PRIMARY key(id)
)

CREATE TABLE employee(
departmentid int(10) not null,
name varchar(20),
FOREIGN KEY(departmentid) REFERENCES department(id) on DELETE CASCADE
)

#插入数据
insert into department values(1,'ls','tom'),(2,'dg','jimmy'),(3,'gr','lily'),(4,'rd','john');
INSERT into employee values(1,'aaron'),(2,'bart'),(2,'brook'),(2,'dave'),(2,'elton'),(2,'george'),(3,'niuniu'),(4,'jack');

select * from department;
select * from employee;


#执行结果
select GROUP_CONCAT(employee.name,',') as aa from department join employee on department.id = employee.departmentid and department.leaderName ='jimmy'