一、基础数据
有学生成绩表,包含学生id、学科、成绩
+-------------+----------+--------+
| student_id | subject | score |
+-------------+----------+--------+
| 001 | 语文 | 89 |
| 001 | 数学 | 95 |
| 001 | 英语 | 77 |
| 002 | 语文 | 92 |
| 002 | 数学 | 83 |
| 002 | 英语 | 97 |
| 003 | 语文 | 81 |
| 003 | 数学 | 94 |
| 003 | 英语 | 88 |
+-------------+----------+--------+
二、函数介绍
三、多行转多列(竖表转横表)
原始数据中是一个竖表,每个学生的每个学科一行数据,对其转换成一张横表,即表中学生id为主键,包含语文、数学、英语三列,列值为对应学科分数。
期望结果
+-------------+--------+---------+---------+
| student_id | yuwen | shuxue | yingyu |
+-------------+--------+---------+---------+
| 001 | 89 | 95 | 77 |
| 002 | 92 | 83 | 97 |
| 003 | 81 | 94 | 88 |
+-------------+--------+---------+---------+
1.生成三个科目的成绩列
使用case when语句,对三个科目依次判断,是对应科目的取对应科目成绩,不是对应科目不取值。
执行SQL
select student_id,
case when subject = '语文' then score end as yuwen,
case when subject = '数学' then score end as shuxue,
case when subject = '英语' then score end as yingyu
from t_student_score
执行结果
+-------------+--------+---------+---------+
| student_id | yuwen | shuxue | yingyu |
+-------------+--------+---------+---------+
| 001 | 89 | NULL | NULL |
| 001 | NULL | 95 | NULL |
| 001 | NULL | NULL | 77 |
| 002 | 92 | NULL | NULL |
| 002 | NULL | 83 | NULL |
| 002 | NULL | NULL | 97 |
| 003 | 81 | NULL | NULL |
| 003 | NULL | 94 | NULL |
| 003 | NULL | NULL | 88 |
+-------------+--------+---------+---------+
2.聚合,将每个学生的科目放到同一行
使用聚合函数,将学生不同科目成绩压缩到同一行。
执行SQL
select student_id,
sum(case when subject = '语文' then score end) as yuwen,
sum(case when subject = '数学' then score end) as shuxue,
sum(case when subject = '英语' then score end) as yingyu
from t_student_score
group by student_id
执行结果
+-------------+--------+---------+---------+
| student_id | yuwen | shuxue | yingyu |
+-------------+--------+---------+---------+
| 001 | 89 | 95 | 77 |
| 002 | 92 | 83 | 97 |
| 003 | 81 | 94 | 88 |
+-------------+--------+---------+---------+
四、数据准备
--建表语句
CREATE TABLE IF NOT EXISTS t_student_score
(
student_id string, -- 学生id
subject string, -- 学科
score bigint -- 分数
)
COMMENT '学生成绩表';
insert into t_student_score
values ('001', '语文', 89),
('001', '数学', 95),
('001', '英语', 77),
('002', '语文', 92),
('002', '数学', 83),
('002', '英语', 97),
('003', '语文', 81),
('003', '数学', 94),
('003', '英语', 88);