MySql根据条件分组,然后将某些字段进行拼接
使用了sql函数 GROUP_CONCAT
实现分组后的拼接
查询结果如下:
查询的sql:
select
sys_user.username as '账号',
sys_user.nick_name as '姓名',
GROUP_CONCAT(sys_role.name,'') as '角色',
sys_user.email as '邮箱'
from sys_user
left join sys_user_role
on sys_user.id = sys_user_role.user_id
left join sys_role
on sys_role.id = sys_user_role.role_id
group by
sys_user.username,sys_user.nick_name,sys_user.email
sys_user用户表数据:
select sys_user.username,
sys_user.nick_name,sys_user.email from sys_user
sys_role 角色表数据:
select sys_role.id,sys_role.name from sys_role
sys_user_role 用户角色关联表数据
select id,user_id,role_id from sys_user_role