sql: 将多条记录的一个字段合并为一条记录 GROUP_CONCAT函数使用

一. 需求

          现有用户表,用户角色表,角色表。其中一个用户对应多个角色。需要在查询用户详情的时候将用户所属的角色名称直接查询出来,用逗号分隔。

二. sql建表脚本

   2.1 用户表

CREATE TABLE `user` (
  `uid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(256) DEFAULT NULL COMMENT '账号',
  `password` varchar(256) DEFAULT NULL COMMENT '密码',
  `state` int(11) DEFAULT NULL COMMENT '状态 0 删除 1启用 2停用',
  `nickname` varchar(256) DEFAULT NULL COMMENT '昵称',
  `position` varchar(256) DEFAULT NULL COMMENT '职位',
  `mobile` varchar(32) DEFAULT NULL COMMENT '手机号',
  `email` varchar(256) DEFAULT NULL COMMENT '邮箱',
  `create_uid` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_uid` bigint(20) DEFAULT NULL COMMENT '修改人ID',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='用户表';

   2.2 用户角色表 

CREATE TABLE `role_menu` (
  `rmid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `rid` bigint(20) DEFAULT NULL COMMENT '角色ID',
  `mid` bigint(20) DEFAULT NULL COMMENT '菜单ID',
  `state` int(11) DEFAULT NULL COMMENT '状态: 0 删除 1正常',
  `create_uid` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_uid` bigint(20) DEFAULT NULL COMMENT '修改人ID',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`rmid`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COMMENT='角色菜单关系表';

    2.3 角色表 

CREATE TABLE `role_menu` (
  `rmid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `rid` bigint(20) DEFAULT NULL COMMENT '角色ID',
  `mid` bigint(20) DEFAULT NULL COMMENT '菜单ID',
  `state` int(11) DEFAULT NULL COMMENT '状态: 0 删除 1正常',
  `create_uid` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_uid` bigint(20) DEFAULT NULL COMMENT '修改人ID',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`rmid`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COMMENT='角色菜单关系表';

     2.4 初始化记录

INSERT INTO `ucpaas_operate_paltform`.`user`(`uid`, `username`, `password`, `state`, `nickname`, `position`, `mobile`, `email`, `create_uid`, `create_time`, `update_uid`, `update_time`) VALUES (1, 'qjwyss', 'e10adc3949ba59abbe56e057f20f883e', 1, 'yuanshushu', 'java', '15107550001', '1@ucpaas.com', 0, '2018-09-03 16:40:44', NULL, NULL);

INSERT INTO `ucpaas_operate_paltform`.`user_role`(`urid`, `uid`, `rid`, `state`, `create_uid`, `create_time`, `update_uid`, `update_time`) VALUES (2, 1, 3, 1, 0, '2018-09-03 17:03:54', NULL, NULL);
INSERT INTO `ucpaas_operate_paltform`.`user_role`(`urid`, `uid`, `rid`, `state`, `create_uid`, `create_time`, `update_uid`, `update_time`) VALUES (3, 1, 5, 1, 0, '2018-09-03 17:04:03', NULL, NULL);

INSERT INTO `ucpaas_operate_paltform`.`role`(`rid`, `role_name`, `description`, `state`, `create_uid`, `create_time`, `update_uid`, `update_time`) VALUES (0, '超级管理员', '超管', 1, 0, '2018-08-27 12:09:59', 0, '2018-08-29 17:20:02');
INSERT INTO `ucpaas_operate_paltform`.`role`(`rid`, `role_name`, `description`, `state`, `create_uid`, `create_time`, `update_uid`, `update_time`) VALUES (3, '运营人员3', '运营人员描述3', 0, 0, '2018-08-29 14:56:44', 0, '2018-08-30 11:29:08');
INSERT INTO `ucpaas_operate_paltform`.`role`(`rid`, `role_name`, `description`, `state`, `create_uid`, `create_time`, `update_uid`, `update_time`) VALUES (5, '主管2', '管理很多事2', 1, 0, '2018-08-29 15:37:47', 0, '2018-08-30 11:14:04');

 

三. 使用

3.1 根据用户ID将角色查询出来,显示为一条记录,角色名称用逗号分隔。

SELECT
	GROUP_CONCAT( R.role_name ) AS role_name_list
FROM
	user_role AS UR 
	LEFT JOIN role AS R ON R.rid = UR.rid
WHERE
	UR.state > 0
	AND UR.uid = 1
GROUP BY UR.uid

结果:

 

 

 

 

 

 

 

 

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页