MySQL中行转列与列转行

MySQL中行转列与列转行

1、行转列

行转列，即为将MySQL中原本同一列（字段）下的内容转换为同一行的多个字段。

如上图一张成绩表，进行如下转换：

1、行转列转换后，

转换后，变为如下显示：

明显可以看出，此时将userid分为了一组，每组都有语文、数学、英语、政治这几门课的成绩。

方式一：用IF语句转换

SELECT user_id,
SUM(IF(`subject`='语文',score,0)) as '语文',
SUM(IF(`subject`='数学',score,0)) as '数学',
SUM(IF(`subject`='英语',score,0)) as '英语',
SUM(IF(`subject`='政治',score,0)) as '政治' 
FROM t_score 
GROUP BY user_id

因为不对应的学科，若不加SUM或MAX，用IF求出的结果必然是0，所以需要在IF外嵌套一个SUM或MAX，求出最大值或是总和，即为分组后所求的分数

如语文：分组后，一个编号学生除了“语文”这一字段外，其他科目字段都不是“语文”，因此数学、英语、政治都为0，若不加SUM或者MAX，则数据会显示为0，加了MAX或SUM，就可以取到“语文”这一列的数据

方式二：用CASE…WHEN…THEN语句转换

SELECT user_id,
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 '政治' 
FROM t_score 
GROUP BY user_id

2、行转列，合并字段显示

转换后，变为如下显示：

将科目与分数拼接在一起

SELECT userid,GROUP_CONCAT(`subject`,":",score)AS 成绩 FROM t_score
GROUP BY user_id

3、行转列，并求出各个科目的总成绩和每个学生的总成绩。

转换后，如下图显示：

需要将total列求出来，并求出一个单行total行，与利用UNION与前面三行拼接到一起（UNION 不去重，UNION去重）

SELECT user_id,
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_id
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

2、列转行

即将上面的列，转为下面的行

通过将每个userid对应的多个科目的成绩查出来，通过UNION ALL将结果集加起来

SELECT user_id,'语文' AS course,cn_score AS score FROM tb_score1
UNION ALL
SELECT user_id,'数学' AS course,math_score AS score FROM tb_score1
UNION ALL
SELECT user_id,'英语' AS course,en_score AS score FROM tb_score1
UNION ALL
SELECT user_id,'政治' AS course,po_score AS score FROM tb_score1
ORDER BY user_id

UNION与UNION ALL的区别：
1、去重：UNION会去重，UNION ALL不会
2、排序：UNION会排序，UNION ALL不会
3、效率：UNION ALL不会去重和排序，效率比UNION高