数据库查询中的行转列,在项目开发中用的很多,尤其一些财务报表,本章我们来讨论一下行转列。
什么是行转列
数据的原始数据:
学号 姓名 课程 成绩、
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);
- 通过左连接查询将所有表连接起来
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_idleft 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 赵六
- 使用case语句将每门课转换为列
select s.stu_id '学号',s.stu_name '姓名',-- 判断课程名称如果是语文,就把语文成绩作为列的值,否则值为0case 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_idleft join course c on c.course_id = sc.course_id;查询结果:学号 姓名 语文 数学 英语2 小周 60 0 02 小周 0 70 02 小周 0 0 861 小张 80 0 01 小张 0 82 01 小张 0 0 843 王五 83 0 03 王五 0 77 03 王五 0 0 894 赵六 0 0 0
- 按学号分组
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_idleft join course c on c.course_id = sc.course_idgroup by s.stu_id;查询结果:学号 姓名 语文 数学 英语1 小张 80 0 02 小周 60 0 03 王五 83 0 04 赵六 0 0 0
- 使用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_idleft join course c on c.course_id = sc.course_idgroup by s.stu_id;查询结果:学号 姓名 语文 数学 英语1 小张 80 82 842 小周 60 70 863 王五 83 77 894 赵六 0 0 0
以上就是一种常用的行转列实现方式,希望对大家有帮助。