玩转MySQL（五）常见的SQL面试问题：行转列

超详细的Java知识点路线图

---

---

前言

在面试时经常会问数据库的行转列问题，其实在项目开发中用的很多，尤其一些财务报表，这其实是考验大家对SQL查询的掌握程度，本章我们来讨论一下行转列。

什么是行转列

数据的原始数据:

学号 姓名 课程 成绩 
001 张三 语文 60 
001 张三 数学 89 
001 张三 英语 88 
002 李四 语文 88 
002 李四 数学 66 
002 李四 英语 90 

转换后的效果:

学号 姓名 语文 数学 英语 
001 张三  60  89  88 
002 李四  88  66  90 

行转列需要的技术

1）连接查询

使用inner join或left join将多表连接在一起

2）分组查询

使用group by子句对特定列进行分组，如上面案例中的学号

3）聚合函数

使用max函数取得有值的数据

4）CASE语句

想CASE语句可以嵌入SQL语句中，用于条件判断
语法：
CASE
WHEN 条件 THEN 结果
WHEN 条件 THEN 结果
WHEN 条件 THEN 结果
ELSE 结果
END
或
CASE 列
WHEN 值 THEN 结果
WHEN 值 THEN 结果
WHEN 值 THEN 结果
ELSE 结果
END

行转列的实现

表结构：

drop table if exists student;
create table student(
	stu_id int primary key auto_increment,
	stu_name varchar(20),
	stu_gender varchar(20),
	stu_age int
);
drop table if exists course;
create table course(
	course_id int primary key auto_increment,
	course_name varchar(20)
);
drop table if exists score;
create table score(
	score_id int primary key auto_increment,
	stu_id int,
	course_id int,
	score int,
	constraint fk_stu_id foreign key (stu_id) references student(stu_id),
	constraint fk_course_id foreign key (course_id) references course(course_id)
);
insert into student(stu_name,stu_gender,stu_age)
values('张三','男',15),('李四','男',15),('王五','男',15),('赵六','男',15);
insert into course(course_name)
values('语文'),('数学'),('英语');
insert into score(stu_id,course_id,score)
values(1,1,80),(1,2,82),(1,3,84),(2,1,60),
(2,2,70),(2,3,86),(3,1,83),(3,2,77),(3,3,89);

1. 通过左连接查询将所有表连接起来

select s.stu_id '学号',s.stu_name '姓名',c.course_name '课程',sc.score '分数'
from student s left join score sc on s.stu_id = sc.stu_id
left join course c on c.course_id = sc.course_id;

查询结果：
学号 姓名   语文 数学 英语
2	小周	语文	60
2	小周	数学	70
2	小周	英语	86
1	小张	语文	80
1	小张	数学	82
1	小张	英语	84
3	王五	语文	83
3	王五	数学	77
3	王五	英语	89
4	赵六

2. 使用case语句将每门课转换为列

select s.stu_id '学号',s.stu_name '姓名',
	-- 判断课程名称如果是语文，就把语文成绩作为列的值，否则值为0
case c.course_name when '语文' then sc.score else 0 end '语文',
case c.course_name when '数学' then sc.score else 0 end '数学',
case c.course_name when '英语' then sc.score else 0 end '英语'
from student s left join score sc on s.stu_id = sc.stu_id
left join course c on c.course_id = sc.course_id;

查询结果：
学号 姓名   语文 数学 英语
2	小周	60	0	0
2	小周	0	70	0
2	小周	0	0	86
1	小张	80	0	0
1	小张	0	82	0
1	小张	0	0	84
3	王五	83	0	0
3	王五	0	77	0
3	王五	0	0	89
4	赵六	0	0	0

3. 按学号分组

select s.stu_id '学号',s.stu_name '姓名',
(case c.course_name when '语文' then sc.score else 0 end) '语文',
(case c.course_name when '数学' then sc.score else 0 end) '数学',
(case c.course_name when '英语' then sc.score else 0 end) '英语'
from student s left join score sc on s.stu_id = sc.stu_id
left join course c on c.course_id = sc.course_id
group by s.stu_id;

查询结果：
学号 姓名   语文 数学 英语
1	小张	80	0	0
2	小周	60	0	0
3	王五	83	0	0
4	赵六	0	0	0

4. 使用Max和Sum统计分数获得每门课大于0的分数

select s.stu_id '学号',s.stu_name '姓名',
max(case c.course_name when '语文' then sc.score else 0 end) '语文',
max(case c.course_name when '数学' then sc.score else 0 end) '数学',
max(case c.course_name when '英语' then sc.score else 0 end) '英语'
from student s left join score sc on s.stu_id = sc.stu_id
left join course c on c.course_id = sc.course_id
group by s.stu_id;

查询结果：
学号 姓名   语文 数学 英语
1	小张	80	82	84
2	小周	60	70	86
3	王五	83	77	89
4	赵六	0	0	0

总结

以上就是一种常用的行转列实现方式，如果对大家有帮助，左下角点个赞 ：）

---

大家如果需要学习其他Java知识点，戳这里 超详细的Java知识点汇总