MySQL 行转列与列传行的使用

野性酷女 2023-10-10 11:29 12阅读 0赞

#### 文章目录 ####

*   *  一、MySQL版本
     *  二、行转列
     *   *  1.行转列 方法一
         *   *  1.1 建表语句
             *  1.2 数据展示
             *  1.3 编写SQL
             *  1.4 执行结果
             *  1.5 计算个人总分数和每门课程平均分数
             *   *  1.5.1 WITH ROLLUP + sum() 方法一
                 *  1.5.2 UNION + sum() 方法二
                 *  1.5.2 直接使用sum()，不再使用子查询 方法三
         *  2.行转列 方法二
         *   *  2.1 编写SQL
             *  2.2 执行结果
         *  3.行转列 方法三
         *   *  3.1 编写SQL
             *  3.2执行结果
     *  三、列转行
     *   *  3.1 建表语句
         *  3.2 数据展示
         *  3.3 编写SQL
         *  3.4 执行结果

### 一、MySQL版本 ###

*  MySQL版本 8.0.11

### 二、行转列 ###

#### 1.行转列 方法一 ####

##### 1.1 建表语句 #####

-- 建表语句
    CREATE TABLE `student` (
      `id` int(7) NOT NULL COMMENT '学号',
      `stu_name` varchar(20) NOT NULL COMMENT '学生姓名',
      `course_name` varchar(100) NOT NULL COMMENT '课程名称',
      `score` float DEFAULT NULL COMMENT '分数',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    
    -- 插入数据
    insert  into `student`(`id`,`stu_name`,`course_name`,`score`) values 
    (1,'张三','数学',67),
    (2,'张三','语文',88),
    (3,'张三','英语',91),
    (4,'李四','数学',73),
    (5,'李四','语文',79),
    (6,'李四','英语',82),
    (7,'王五','数学',95),
    (8,'王五','语文',91),
    (9,'王五','英语',63),
    (10,'赵六','数学',84),
    (11,'赵六','语文',89),
    (12,'赵六','英语',80),
    (13,'田七','数学',77),
    (14,'田七','语文',81),
    (15,'田七','英语',92),
    (16,'周八','数学',50),
    (17,'周八','语文',42),
    (18,'周八','英语',62);

##### 1.2 数据展示 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70]

##### 1.3 编写SQL #####

SELECT
        stu_name ,
        MAX(CASE course_name WHEN '数学' THEN score ELSE NULL END) '数学1',
        MAX(CASE course_name WHEN '语文' THEN score ELSE NULL END) '语文1',
        MAX(CASE course_name WHEN '英语' THEN score ELSE NULL END) '英语1'
    FROM 
    	student 
    GROUP BY 
    	stu_name ;

##### 1.4 执行结果 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 1]

##### 1.5 计算个人总分数和每门课程平均分数 #####

*  计算个人总分数 和 每门课程平均分数

###### 1.5.1 WITH ROLLUP + sum() 方法一 ######

*  先查询出个人总分数作为子表，然后行转列操作计算每门课程的平均分
 *  编写SQL

SELECT
    	IFNULL( stu_name, '平均分(方式一)' ) stu_name,
    	FORMAT( AVG( CASE course_name WHEN '数学' THEN score ELSE NULL END ), 2 ) '数学1',
    	FORMAT( AVG( CASE course_name WHEN '语文' THEN score ELSE NULL END ), 2 ) '语文1',
    	FORMAT( AVG( CASE course_name WHEN '英语' THEN score ELSE NULL END ), 2 ) '英语1',
    	SUM( CASE course_name WHEN 'selfTotal' THEN score ELSE NULL END ) 'selfTotal' 
    FROM
    	(
    SELECT
    	stu_name,
    	IFNULL( course_name, 'selfTotal' ) course_name,
    	SUM( score ) score 
    FROM
    	student 
    GROUP BY
    	stu_name,
    	course_name WITH ROLLUP 
    HAVING
    	stu_name IS NOT NULL 
    	) t1 
    GROUP BY
    	stu_name WITH ROLLUP

*  执行结果：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 2]

###### 1.5.2 UNION + sum() 方法二 ######

*  编写SQL

SELECT
    	IFNULL( stu_name, '平均分(方式二)' ) stu_name,
    	SUM( CASE course_name WHEN '数学' THEN score ELSE NULL END ) '数学1',
    	SUM( CASE course_name WHEN '语文' THEN score ELSE NULL END ) '语文1',
    	SUM( CASE course_name WHEN '英语' THEN score ELSE NULL END ) '英语1',
    	SUM( score ) 'selfTotal' 
    FROM
    	student 
    GROUP BY
    	stu_name UNION
    SELECT
    	'平均分(方式二)',
    	FORMAT( AVG( CASE course_name WHEN '数学' THEN score ELSE NULL END ), 2 ) '数学1',
    	FORMAT( AVG( CASE course_name WHEN '语文' THEN score ELSE NULL END ), 2 ) '语文1',
    	FORMAT( AVG( CASE course_name WHEN '英语' THEN score ELSE NULL END ), 2 ) '英语1',
    	SUM( score ) 'selfTotal' 
    FROM
    	student

*  执行结果  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 3]

###### 1.5.2 直接使用sum()，不再使用子查询 方法三 ######

*  编写SQL

SELECT
    	IFNULL( stu_name, '平均分(方式三)' ) AS stu_name,
    	FORMAT( AVG( CASE course_name WHEN '数学' THEN score ELSE NULL END ), 2 ) '数学1',
    	FORMAT( AVG( CASE course_name WHEN '语文' THEN score ELSE NULL END ), 2 ) '语文1',
    	FORMAT( AVG( CASE course_name WHEN '英语' THEN score ELSE NULL END ), 2 ) '英语1',
    	SUM( score ) AS selfTotal 
    FROM
    	student 
    GROUP BY
    	stu_name WITH ROLLUP;

*  执行结果  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 4]

#### 2.行转列 方法二 ####

##### 2.1 编写SQL #####

SELECT DISTINCT
    	t2.stu_name,
    	( SELECT score FROM student t1 WHERE t1.stu_name = t2.stu_name AND t1.course_name = '数学' ) AS '数学2',
    	( SELECT score FROM student t1 WHERE t1.stu_name = t2.stu_name AND t1.course_name = '语文' ) AS '语文2',
    	( SELECT score FROM student t1 WHERE t1.stu_name = t2.stu_name AND t1.course_name = '英语' ) AS '英语2' 
    FROM
    	student t2;

##### 2.2 执行结果 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 5]

#### 3.行转列 方法三 ####

*  不再使用 case…when…then…else…end语法，直接使用if()

##### 3.1 编写SQL #####

SELECT
    	stu_name,
    	SUM( IF ( course_name = '数学', score, 0 ) ) AS '语文3',
    	SUM( IF ( course_name = '语文', score, 0 ) ) AS '数学3',
    	SUM( IF ( course_name = '英语', score, 0 ) ) AS '英语3' 
    FROM
    	student 
    GROUP BY
    	stu_name

##### 3.2执行结果 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 6]

### 三、列转行 ###

#### 3.1 建表语句 ####

-- 建表语句
    CREATE TABLE `student1` (
      `id` int(5) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `stu_name` varchar(32) DEFAULT NULL COMMENT '学生名称',
      `language` varchar(32) DEFAULT NULL COMMENT '语文',
      `math` varchar(32) DEFAULT NULL COMMENT '数学',
      `english` varchar(32) DEFAULT NULL COMMENT '英语',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    -- 插入数据
    insert  into `student1`(`id`,`stu_name`,`language`,`math`,`english`) values 
    (1,'张三','67','88','91'),
    (2,'李四','73','79','82'),
    (3,'王五','95','91','63'

#### 3.2 数据展示 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 7]

#### 3.3 编写SQL ####

SELECT stu_name,'语文' AS course_name,LANGUAGE AS score FROM student1
    UNION ALL
    SELECT stu_name,'数学' AS course_name,math AS score FROM student1
    UNION ALL
    SELECT stu_name,'英语' AS course_name,english AS score FROM student1
    ORDER BY stu_name

#### 3.4 执行结果 ####

*  执行结果：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 8]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200813154903119.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M=,size_16,color_FFFFFF,t_70#
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200813162820531.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M=,size_16,color_FFFFFF,t_70##
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200813164622848.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M=,size_16,color_FFFFFF,t_70##
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200813164539593.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M=,size_16,color_FFFFFF,t_70##
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200813165234642.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M=,size_16,color_FFFFFF,t_70##
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200813165446586.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M=,size_16,color_FFFFFF,t_70##
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200813170108286.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M=,size_16,color_FFFFFF,t_70##
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200813170309910.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M=,size_16,color_FFFFFF,t_70##
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200813170733434.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXppY2M=,size_16,color_FFFFFF,t_70##