MysSQL 行转列以及列转行（学生的各科成绩，以及总分和平均分）-526互联

一、行转列

即将原本同一列下多行的不同内容作为多个字段，输出对应内容。

1. 建表语句

-- 新建学生成绩表
DROP TABLE IF EXISTS tb_score;
create table tb_score(
    id int(11) not null auto_increment,
    user_no VARCHAR(32) not null comment '学生工号',
    subject varchar(32) comment '课程',
    score int(8) COMMENT '成绩',
    primary key(id)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

2. 插入数据

-- 插入测试数据
INSERT INTO tb_score(user_no,subject,score) VALUES('001','语文',31);
INSERT INTO tb_score(user_no,subject,score) VALUES('001','数学',32);
INSERT INTO tb_score(user_no,subject,score) VALUES('001','英语',33);
 
INSERT INTO tb_score(user_no,subject,score) VALUES('002','语文',41);
INSERT INTO tb_score(user_no,subject,score) VALUES('002','数学',42);
INSERT INTO tb_score(user_no,subject,score) VALUES('002','英语',43);
 
INSERT INTO tb_score(user_no,subject,score) VALUES('003','语文',51);
INSERT INTO tb_score(user_no,subject,score) VALUES('003','数学',52);
INSERT INTO tb_score(user_no,subject,score) VALUES('003','英语',53);
 
INSERT INTO tb_score(user_no,subject,score) VALUES('004','语文',61);
INSERT INTO tb_score(user_no,subject,score) VALUES('004','数学',62);
INSERT INTO tb_score(user_no,subject,score) VALUES('004','英语',63);
INSERT INTO tb_score(user_no,subject,score) VALUES('004','政治',64);

3. 查询数据表中的内容（即转换前的结果）

-- 查询转换前的数据
SELECT * from tb_score;

4. 转换后的结果

分析：

这里行转列是将原来的subject字段的多行内容选出来，作为结果集中的不同列，并根据user_no进行分组显示对应的score。几种方法汇总如下：

使用case...when....then 进行行转列

SELECT 
tb.user_no as '学号',
SUM(CASE subject when '语文' then score else 0 end) as '语文',
SUM(CASE subject when '数学' then score else 0 end) as '数学',
SUM(CASE subject when '英语' then score else 0 end) as '英语',
SUM(CASE subject when '政治' then score else 0 end) as '政治',
sum(score) as '总分',
avg(score) as '平均分'
from tb_score tb GROUP BY tb.user_no;

使用IF() 进行行转列

SELECT 
tb.user_no as '学号',
SUM(if (`subject` = '语文',score,0)) as '语文',
SUM(if (`subject` = '数学',score,0)) as '数学',
SUM(if (`subject` = '英语',score,0)) as '英语',
SUM(if (`subject` = '政治',score,0)) as '政治',
sum(score) as '总分',
avg(score) as '平均分'
from tb_score tb GROUP BY tb.user_no;

-- 合并字段显示：利用group_concat()
select tb.user_no,GROUP_CONCAT(tb.`subject`,':',tb.score) from tb_score tb GROUP BY tb.user_no;

注意点：

（1）SUM() 是为了能够使用GROUP BY根据user_no进行分组，因为每一个user_no对应的subject="语文"的记录只有一条，所以SUM() 的值就等于对应那一条记录的score的值。

假如user_no='001' and subject='语文' 的记录有两条，则此时SUM() 的值将会是这两条记录的和，同理，使用Max()的值将会是这两条记录里面值最大的一个。但是正常情况下，一个user_no对应一个subject只有一个分数，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函数都可以达到行转列的效果。

（2）IF(`subject`='语文',score,0) 作为条件，即对所有subject='语文'的记录的score字段进行SUM()、MAX()、MIN()、AVG()操作，如果score没有值则默认为0。

利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为Total

SELECT IFNULL(user_no,'total') AS '学号',
SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(IF(`subject`='total',score,0)) AS total
FROM(
    SELECT user_no,IFNULL(`subject`,'total') AS `subject`,SUM(score) AS score
    FROM tb_score
    GROUP BY user_no,`subject`
    WITH ROLLUP
    HAVING user_no IS NOT NULL
)AS A 
GROUP BY user_no
WITH ROLLUP;

运行后的结果为：

利用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total

SELECT user_no as 学号,
    SUM(IF(`subject`='语文',score,0)) AS 语文,
    SUM(IF(`subject`='数学',score,0)) AS 数学,
    SUM(IF(`subject`='英语',score,0)) AS 英语,
    SUM(IF(`subject`='政治',score,0)) AS 政治,
    SUM(score) AS total 
FROM tb_score
GROUP BY user_no
UNION
SELECT 'total',SUM(IF(`subject`='语文',score,0)) AS 语文,
    SUM(IF(`subject`='数学',score,0)) AS 数学,
    SUM(IF(`subject`='英语',score,0)) AS 英语,
    SUM(IF(`subject`='政治',score,0)) AS 政治,
    SUM(score) FROM tb_score

运行后的结果为：

利用SUM(IF()) 生成列，直接生成结果不再利用子查询

SELECT IFNULL(user_no,'total') AS user_no,
    SUM(IF(`subject`='语文',score,0)) AS 语文,
    SUM(IF(`subject`='数学',score,0)) AS 数学,
    SUM(IF(`subject`='英语',score,0)) AS 英语,
    SUM(IF(`subject`='政治',score,0)) AS 政治,
    SUM(score) AS total 
FROM tb_score
GROUP BY user_no WITH ROLLUP;

运行后的结果为：

动态，适用于列不确定情况

SET @AA='';
SELECT @AA :=CONCAT(@AA,'sum(if(subject= \'',SUBJECT,'\',score,0)) as ',SUBJECT, ',') AS aa FROM (SELECT DISTINCT SUBJECT FROM tb_score) A ;

SET @BB = CONCAT('select ifnull(user_no,\'TOTAL\')as user_no,',@AA,' sum(score) as TOTAL from tb_score group by user_no WITH ROLLUP');
-- SELECT @BB;

PREPARE stmt FROM @BB;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

运行后的结果为：

合并字段显示：利用group_concat()

SELECT user_no,
    GROUP_CONCAT(`subject`,":",score)AS 成绩 
FROM tb_score
GROUP BY user_no

运行后的结果为：

group_concat()，手册上说明:该函数返回带有来自一个组的连接的非NULL值的字符串结果。
比较抽象，难以理解。通俗点理解，其实是这样的：group_concat()会计算哪些行属于同一组，将属于同一组的列显示出来。要返回哪些列，由函数参数(就是字段名)决定。分组必须有个标准，就是根据group by指定的列进行分组。

结论：group_concat()函数可以很好的建属于同一分组的多个行转化为一个列。

二、列转行

1. 建表语句

-- 新建另外一张学生信息表
DROP TABLE IF EXISTS tb_score1;
CREATE TABLE tb_score1(
    id INT(11) NOT NULL AUTO_INCREMENT,
    user_no VARCHAR(32) COMMENT '学生工号',
    yuwen INT(11) COMMENT '语文成绩',
    shuxue INT(11) COMMENT '数学成绩',
    yingyu INT(11) COMMENT '英语成绩',
    zhengzhi INT(11) COMMENT '政治成绩',
PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

2. 插入数据

-- 插入数据
INSERT INTO tb_score1(user_no,yuwen,shuxue,yingyu,zhengzhi) VALUES('001',31,32,33,0);
INSERT INTO tb_score1(user_no,yuwen,shuxue,yingyu,zhengzhi) VALUES('002',41,42,43,0);
INSERT INTO tb_score1(user_no,yuwen,shuxue,yingyu,zhengzhi) VALUES('003',51,52,53,0);
INSERT INTO tb_score1(user_no,yuwen,shuxue,yingyu,zhengzhi) VALUES('004',61,62,63,64);

3. 查询数据表中的内容（即转换前的结果）

-- 查询数据
SELECT * from tb_score1;

4. 转换后的结果

分析：

本质是将user_no的每个科目分数分散成一条记录显示出来。

利用UNION ALL将结果集加起来

-- 计算/汇总
SELECT user_no AS '学号','语文' AS '课程',yuwen AS '成绩' FROM tb_score1
UNION ALL
SELECT user_no AS '学号','数学' AS '课程',shuxue AS '成绩' FROM tb_score1
UNION ALL
SELECT user_no AS '学号','英语' AS '课程',yingyu AS '成绩' FROM tb_score1
UNION ALL
SELECT user_no AS '学号','政治' AS '课程',zhengzhi AS '成绩' FROM tb_score1
ORDER BY '学号';