MySQL行转列
在数据库操作中,有时我们需要将行数据转换为列数据,这在生成报表或进行数据汇总时尤为常见。本文将以一个学生成绩表为例,演示在MySQL中实现行转列的几种方法。
示例数据
假设我们有一个学生成绩表 score,包含以下三列:sid(学生ID)、cid(科目ID)和score(分数)。表中的数据如下:
sid | cid | score |
---|---|---|
1 | 1 | 90 |
1 | 2 | 85 |
1 | 3 | 95 |
2 | 1 | 88 |
2 | 2 | 92 |
2 | 3 | 87 |
3 | 1 | 95 |
3 | 2 | 89 |
3 | 3 | 91 |
我们的目标是将这些行数据转换为列数据,使得每个学生的成绩按科目显示在不同的列中。
方法1:使用 GROUP BY 和 CASE 语句
这是最常用的方法之一,通过使用 GROUP BY 和 CASE 语句,可以将行数据聚合并转换为列数据。
SELECT sid,MAX(CASE WHEN cid = 1 THEN score ELSE NULL END) AS a,MAX(CASE WHEN cid = 2 THEN score ELSE NULL END) AS b,MAX(CASE WHEN cid = 3 THEN score ELSE NULL END) AS c
FROM score
GROUP BY sid;
结果将是:
sid | a | b | c |
---|---|---|---|
1 | 90 | 85 | 95 |
2 | 88 | 92 | 87 |
3 | 95 | 89 | 91 |
方法2:使用子查询和聚合函数
SELECT s1.sid,s1.score AS a,s2.score AS b,s3.score AS c
FROM (SELECT sid, score FROM score WHERE cid = 1) s1
LEFT JOIN (SELECT sid, score FROM score WHERE cid = 2) s2 ON s1.sid = s2.sid
LEFT JOIN (SELECT sid, score FROM score WHERE cid = 3) s3 ON s1.sid = s3.sid;