MySQL 行转列列转行

谁借莪１个温暖的怀抱￠ 2022-11-19 06:29 293阅读 0赞

## 场景 ##

有学生成绩表score如下：  
![在这里插入图片描述][20210414224116411.png]  
要求通过一条 SQL 语句将学生成绩表，转为如下结果：  
![在这里插入图片描述][20210414224233324.png]

## 创建测试数据 ##

DROP TABLE IF EXISTS score;
    CREATE TABLE score(
        id INT(11) NOT NULL auto_increment,
        name VARCHAR(20) COMMENT '姓名',
        subject VARCHAR(20) COMMENT '科目',
        score DOUBLE(10,1) COMMENT '成绩',
        PRIMARY KEY(id)
    )ENGINE = INNODB DEFAULT CHARSET = utf8;
    INSERT INTO score(name,subject,score) VALUES ('张三','语文',81);
    INSERT INTO score(name,subject,score) VALUES ('张三','数学',75);
    INSERT INTO score(name,subject,score) VALUES ('李四','语文',90);
    INSERT INTO score(name,subject,score) VALUES ('李四','数学',81);

## 答案 ##

1.  使用 case… when…then 进行行转列。

SELECT
    	`name` AS '姓名',
    	SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) AS '语文',
    	SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) AS '数学'
    FROM
    	`score`
    	GROUP BY `name`

注意：SUM()是为了使用GROUP BY根据name分组，由于每一个name对应的subject='语文’的记录只有一条，所以SUM()的结果就等于对应记录score的值。同理，可以将SUM()换成MAX()、MIN()、AVG()等聚合函数。  
当然，某个人对应的课程如果有两条，则SUM()的值将会是这两条结果的和。  
2. 使用 IF() 进行行转列。

SELECT
    	`name` AS '姓名',
    	SUM( IF ( `subject` = '语文', score, 0)) AS '语文',
    	SUM( IF ( `subject` = '数学', score, 0)) AS '数学' 
    FROM
    	`score` 
    GROUP BY
    	`name`

1.  动态，列不确定

SET @EE='';
    select @EE :=CONCAT(@EE,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM score) A ;
    
    SET @QQ = CONCAT('select ifnull(`name`,\'total\')as name,',@EE,' sum(score) as collect from score group by name WITH ROLLUP');
    -- SELECT @QQ;
    
    PREPARE stmt FROM @QQ;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

1.  动态

SET @SQL = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(sc.subject=''',s.subject,''',sc.score,0)) AS ''',s.subject,'''')) INTO @SQL FROM score s;
    
    SET @SQL = CONCAT('SELECT name,',@SQL,' FROM score sc GROUP BY name');
    PREPARE STMT FROM @SQL;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;

## 扩展 ##

1.  想要获取班级语文总分，数学总分，如下所示：  
    ![在这里插入图片描述][20210415003715711.png]  
    答案为：  
    方案一：使用 GROUP BY…WITH ROLLUP

SELECT
    	IFNULL(`name`,'total') as 'name',
    	SUM(IF(`subject`='语文',score, 0 )) AS '语文' ,
    	SUM(IF(`subject`='数学',score,0)) AS '数学'
    FROM
    	`score`
    	GROUP BY `name` WITH ROLLUP

方案二：使用union

SELECT
    	`name` AS '姓名',
    	SUM( IF ( `subject` = '语文', score, 0 ) ) AS '语文',
    	SUM( IF ( `subject` = '数学', score, 0 ) ) AS '数学' 
    FROM
    	`score` 
    GROUP BY
    	`name` 
    	UNION
    SELECT
    	'total',
    	SUM( IF ( `subject` = '语文', score, 0 ) ) AS '语文',
    	SUM( IF ( `subject` = '数学', score, 0 ) ) AS '数学' 
    FROM
    	`score`

1.  想要获取某个人的总成绩，如下述所示：  
    ![在这里插入图片描述][20210415004858578.png]

答案为：

SELECT
    	IFNULL( `name`, 'total' ) AS 'name',
    	SUM( IF ( `subject` = '语文', score, 0 ) ) AS '语文',
    	SUM( IF ( `subject` = '数学', score, 0 ) ) AS '数学',
    	SUM( score ) AS collect
    FROM
    	`score` 
    GROUP BY
    	`name` WITH ROLLUP

[20210414224116411.png]: /images/20221022/a1df2aaa34da4ef9a461821c57b82936.png
[20210414224233324.png]: /images/20221022/0cb716394c324417b490d450a36cddf2.png
[20210415003715711.png]: /images/20221022/d9301632bcef4b8182c367892e505abe.png
[20210415004858578.png]: /images/20221022/e604e765169f4b408d54ad0d56c7ff92.png