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

结果:

 

 

 

 

 

 

 

 

 

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页
评论 5

打赏作者

请叫我猿叔叔

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值