MySQL行转列

╰+哭是因爲堅強的太久メ 2023-06-09 14:24 380阅读 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,
        MAX(CASE COURSE_CODE WHEN 'Chinese' THEN score ELSE 0 END ) Chinese,
        MAX(CASE COURSE_CODE WHEN 'math' THEN score ELSE 0 END ) math,
        MAX(CASE COURSE_CODE WHEN 'English' THEN score ELSE 0 END ) English
    FROM GRADE
    GROUP BY USER_NAME;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTczOTcyMA_size_16_color_FFFFFF_t_70]: /images/20230531/1d25dab5ba164457b14839f020dbae5d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTczOTcyMA_size_16_color_FFFFFF_t_70 1]: /images/20230531/918ab6b179b04efca4fb763e0da4c1c9.png