最近工作中遇到了几次行转列与列转行的相关问题,虽然只是小问题,但却是数据库数据处理中比较经典的案例,自己做下笔记,以做温习之用。
一、行转列小案例
测试表结构:
create table 成绩表
(
姓名 VARCHAR2(100),
课程 VARCHAR2(100),
分数 NUMBER(5)
)
测试数据:
insert into 成绩表 (姓名, 课程, 分数) values ('小明', '数据结构', 82);
insert into 成绩表 (姓名, 课程, 分数) values ('小明', '操作系统', 84);
insert into 成绩表 (姓名, 课程, 分数) values ('小明', '计算机应用', 85);
insert into 成绩表 (姓名, 课程, 分数) values ('小李', '数据结构', 92);
insert into 成绩表 (姓名, 课程, 分数) values ('小李', '操作系统', 94);
insert into 成绩表 (姓名, 课程, 分数) values ('小李', '计算机应用', 96);
insert into 成绩表 (姓名, 课程, 分数) values ('小红', '数据结构', 75);
insert into 成绩表 (姓名, 课程, 分数) values ('小红', '操作系统', 76);
insert into 成绩表 (姓名, 课程, 分数) values ('小红', '计算机应用', 71);
希望实现效果:
解决方案:可以通过group by对学生进行分组查询,并结合sum()、decode()函数就可以实现行转列的功能,具体实现SQL如下:
select t.姓名,
sum(decode(t.课程, '数据结构', t.分数, null)) 数据结构,
sum(decode(t.课程, '操作系统', t.分数, null)) 操作系统,
sum(decode(t.课程, '计算机应用', t.分数, null)) 计算机应用
from 成绩表 t
group by t.姓名
二、列转行小案例
创建测试表:
create table 成绩表02
as
select t.姓名,
sum(decode(t.课程, '数据结构', t.分数, null)) 数据结构,
sum(decode(t.课程, '操作系统', t.分数, null)) 操作系统,
sum(decode(t.课程, '计算机应用', t.分数, null)) 计算机应用
from 成绩表 t
group by t.姓名
希望实现效果:
解决方案:可以使用union all实现该功能,具体实现SQL如下:
select t.姓名, '数据结构' 课程, t.数据结构 分数 from 成绩表02 t
union all
select t.姓名, '操作系统', t.操作系统 from 成绩表02 t
union all
select t.姓名, '计算机应用', t.计算机应用 from 成绩表02 t
order by 1