mysql分组合并字段拼接字符串，并设置拼接符号，并去重

测试数据准备:

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_group_test
-- ----------------------------
DROP TABLE IF EXISTS `t_group_test`;
CREATE TABLE `t_group_test` (
  `groupid` int(255) DEFAULT NULL,
  `stu_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of t_group_test
-- ----------------------------
INSERT INTO `t_group_test` VALUES ('1', '小红');
INSERT INTO `t_group_test` VALUES ('1', '小华');
INSERT INTO `t_group_test` VALUES ('2', '小明');
INSERT INTO `t_group_test` VALUES ('3', '小绿');
INSERT INTO `t_group_test` VALUES ('3', '小绿');
INSERT INTO `t_group_test` VALUES ('2', '小明');

查询语句：

SELECT groupid 分组, GROUP_CONCAT(
DISTINCT stu_name
SEPARATOR '\n' #可以设置不同成员分割符
) 小组成员 FROM `t_group_test`
GROUP BY groupid;

效果：

推导复杂语句：

select
    sn.uuid 批次,o.OrgName 单位名称,d.DepartmentName 部门名称,s.SystemName 系统名称,s.SystemShortName 系统名称（简称）,sn.TableNameEN 数据表英文名,sn.TableNameCN 数据表中文名,sn.tabledecription 数据表业务描述,
		case when sn.Type = 1 then '字段级' when sn.Type = 0 then '表级' end  负面清单类型,
		GROUP_CONCAT(
			DISTINCT case when sd.SensitiveFieldsNum = 1 then '个人隐私' when sd.SensitiveFieldsNum = 2 then '商业秘密' when sd.SensitiveFieldsNum = 3 then '商业秘密及个人隐私' end
			SEPARATOR '\n'
		) 敏感字段类型,
		GROUP_CONCAT(
			DISTINCT sd.SensitiveFields
			SEPARATOR '\n'
		) 敏感字段信息
    FROM `sharingdata_nav` sn
    LEFT JOIN sharingdata sd on sn.uuid=sd.uuid and sn.OrgID=sd.OrgID and sn.OrgNameIn=sd.OrgNameIn
    and sn.DepartmentId=sd.DepartmentId and sn.DepartmentNameIn=sd.DepartmentNameIn and sn.SystemId=sd.SystemId
    and sn.SystemNameIn=sd.SystemNameIn and sn.TableNameEN=sd.TableNameEN and sn.TableNameCN=sd.TableNameCN
    and sn.tabledecription=sd.tabledecription and sn.negativecheck=sd.negativecheck
    and sn.`Status` = 1
    LEFT JOIN org o on sd.OrgID = o.OrgID and o.`Status` = 1
    LEFT JOIN department d on d.DepartmentID = sd.DepartmentId and d.`Status` = 1
    LEFT JOIN systeminfo s on s.id = sd.SystemId and s.`Status` = 1
    where 1=1 and sd.`Status` = 2
		and sd.uuid =  '20210823003'
		and sd.orgid = 2
		GROUP BY sn.uuid,o.OrgName,d.DepartmentName,s.SystemName,s.SystemShortName,sn.TableNameEN,sn.TableNameCN,sn.tabledecription,sn.Type;

注意：本文归作者所有，未经作者允许，不得转载