MySQL多行合并逗号分隔

深碍√TFBOYSˉ_ 2023-06-09 14:28 59阅读 0赞

`图一`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTczOTcyMA_size_16_color_FFFFFF_t_70]  
`图二`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTczOTcyMA_size_16_color_FFFFFF_t_70 1]

# 多行合并 #

把 **图一** 的展示效果转换成 **图二** 的展示效果  
1、创建成绩表（GRADE）

CREATE TABLE GRADE (
      ID int NOT NULL AUTO_INCREMENT,
      USER_NAME varchar(20) NOT NULL,
      COURSE varchar(20) NOT NULL,
    	COURSE_CODE varchar(20) NOT NULL,
      SCORE float DEFAULT '0',
      PRIMARY KEY (ID)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2、新增测试数据

INSERT INTO GRADE(USER_NAME, COURSE, COURSE_CODE, SCORE) VALUES
    ("张三", "语文", "Chinese", 98),
    ("张三", "数学", "math", 86),
    ("张三", "英语", "English", 92),
    ("李四", "语文", "Chinese", 76),
    ("李四", "数学", "math", 89),
    ("李四", "英语", "English", 32),
    ("王五", "语文", "Chinese", 78),
    ("王五", "数学", "math", 91),
    ("王五", "英语", "English", 56);

3、多行合并逗号分隔SQL

SELECT USER_NAME,GROUP_CONCAT(COURSE) COURSE FROM GRADE
    GROUP BY USER_NAME;

`注：默认分隔符是“,”，若要改为其他分隔符，则使用SEPARATOR来指定，如下图`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTczOTcyMA_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTczOTcyMA_size_16_color_FFFFFF_t_70]: /images/20230531/bc72581983d44215861c4134a33bc732.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTczOTcyMA_size_16_color_FFFFFF_t_70 1]: /images/20230531/689e7463c95d475e81f0ea69a804862d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTczOTcyMA_size_16_color_FFFFFF_t_70 2]: /images/20230531/85092d3a53924f1798f1a78d918c20c5.png