MySQL多列合并

忘是亡心i 2023-06-09 14:29 2阅读 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,COURSE,SCORE,CONCAT(COURSE,'：',SCORE) SCORE_INFO FROM GRADE

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTczOTcyMA_size_16_color_FFFFFF_t_70]: /images/20230531/ac0a6bcf927b4c718b7c86f33d3d0e81.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTczOTcyMA_size_16_color_FFFFFF_t_70 1]: /images/20230531/257ba7185d7a48e5bab4acd03a0b5dd2.png