1200字范文 > Oracle 行列转换学习——行转列

Oracle 行列转换学习——行转列

时间：2020-12-16 13:35:42

相关推荐

Oracle 行列转换学习——行转列

建表

基本表定义

在数据库系统中定义4个实体：学生、教师、课程、选课。

1、学生的属性：学号、姓名、性别、生日

2、教师的属性：工号、姓名、性别、生日、职称

3、课程的属性：课程号码、学时、学分、课程名称

4、选课信息：学号、课程号码、教师工号、成绩

代码

--创建一个学生表：create table xuesheng(Sno CHAR(9) PRIMARY KEY,Sname CHAR(20) UNIQUE,Ssex varCHAR(3) check(Ssex in ('男','女')),Sbirthday date);--创建一个教师表：create table teacher(Tno CHAR(9) PRIMARY KEY,Tname CHAR(20) UNIQUE,Tsex varCHAR(3) check(Tsex in ('男','女')),Tbirthday date,Ttitle char(20));--创建一个课程表：create table kecheng(Kno char(4) PRIMARY KEY,--课程号码Ktime smallint,--课时Kcredit smallint,--课程学分Kname char(40) not null--课程名称);--创建一个选课表：create table xuanke(Sno CHAR(9),--学生学号Kno char(4),--课程号码Tno CHAR(9),--教师工号grade char(4) check(grade>=0 and grade<=100),--课程成绩PRIMARY KEY (Sno,Kno),FOREIGN KEY (Sno) REFERENCES xuesheng(Sno),FOREIGN KEY (Kno) REFERENCES kecheng(Kno));

数据灌输

--学生表insert into xuesheng (Sno,Sname,Ssex,Sbirthday) values ('001', '张三', '男', to_date('0201','yyyymmdd'));insert into xuesheng (Sno,Sname,Ssex,Sbirthday) values ('002', '李四', '男', to_date('19980705','yyyymmdd'));insert into xuesheng (Sno,Sname,Ssex,Sbirthday) values ('003', '王五', '女', to_date('20010703','yyyymmdd'));insert into xuesheng (Sno,Sname,Ssex,Sbirthday) values ('004', '王二', '女', to_date('19990804','yyyymmdd'));insert into xuesheng (Sno,Sname,Ssex,Sbirthday) values ('005', '李二', '男', to_date('20010405','yyyymmdd'));--教师表insert into teacher (tno,tname,tsex,tbirthday,ttitle) values ('101','王一二','男',to_date('19910101','yyyymmdd'),'无');insert into teacher (tno,tname,tsex,tbirthday,ttitle) values ('102','李三四 ','男',to_date('19910101','yyyymmdd'),'无');insert into teacher (tno,tname,tsex,tbirthday,ttitle) values ('103','赵五六 ','男',to_date('19910101','yyyymmdd'),'无');insert into teacher (tno,tname,tsex,tbirthday,ttitle) values ('104','马七八 ','男',to_date('19910101','yyyymmdd'),'无');insert into teacher (tno,tname,tsex,tbirthday,ttitle) values ('105','钱九十 ','男',to_date('19910101','yyyymmdd'),'无');--课程表insert into kecheng (Kno,Ktime ,Kcredit,Kname) values ('001', '30', '4','高数');insert into kecheng (Kno,Ktime ,Kcredit,Kname) values ('002', '40', '1','马原');insert into kecheng (Kno,Ktime ,Kcredit,Kname) values ('003', '48', '2','数据库');insert into kecheng (Kno,Ktime ,Kcredit,Kname) values ('004', '40', '2','体育');insert into kecheng (Kno,Ktime ,Kcredit,Kname) values ('005', '40', '1','英语');--选课信息insert into xuanke (sno,kno,grade) values ('001','001','78');insert into xuanke (sno,kno,grade) values ('001','002','99');insert into xuanke (sno,kno,grade) values ('001','003','99');insert into xuanke (sno,kno,grade) values ('001','004','95');insert into xuanke (sno,kno,grade) values ('001','005','89');insert into xuanke (sno,kno,grade) values ('002','001','56');insert into xuanke (sno,kno,grade) values ('002','002','75');insert into xuanke (sno,kno,grade) values ('002','003','65');insert into xuanke (sno,kno,grade) values ('002','004','66');insert into xuanke (sno,kno,grade) values ('002','005','35');insert into xuanke (sno,kno,grade) values ('003','001','89');insert into xuanke (sno,kno,grade) values ('003','002','75');insert into xuanke (sno,kno,grade) values ('003','003','65');insert into xuanke (sno,kno,grade) values ('003','004','35');insert into xuanke (sno,kno,grade) values ('003','005','65');insert into xuanke (sno,kno,grade) values ('004','001','95');insert into xuanke (sno,kno,grade) values ('004','002','65');insert into xuanke (sno,kno,grade) values ('004','003','75');insert into xuanke (sno,kno,grade) values ('004','004','96');insert into xuanke (sno,kno,grade) values ('004','005','75');insert into xuanke (sno,kno,grade) values ('005','001','96');insert into xuanke (sno,kno,grade) values ('005','002','65');insert into xuanke (sno,kno,grade) values ('005','003','75');insert into xuanke (sno,kno,grade) values ('005','004','63');insert into xuanke (sno,kno,grade) values ('005','005','75');

建表结果

学生表:

教师表:

课程表:

选课表:

行列转换

decode 函数

格式:

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

含义解释：

IF 条件=值1 THENRETURN(翻译值1)ELSIF 条件=值2 THENRETURN(翻译值2)......ELSIF 条件=值n THENRETURN(翻译值n)ELSERETURN(缺省值)END IF

代码：

SELECT sname,sum(decode(kno,'001 ',grade,0)) 高数,--判断kno是否等去'001 '，是则输出grade值，否则输出0 sum(decode(kno,'002 ',grade,0)) 马原,--sum函数起到数据压缩的作用sum(decode(kno,'003 ',grade,0)) 数据库,sum(decode(kno,'004 ',grade,0)) 体育,sum(decode(kno,'005 ',grade,0)) 英语from xuesheng,xuankewhere xuesheng.sno = xuanke.sno --两表连接查询GROUP BY sname--以姓名作为分组ORDER BY sname;--排序--其中decode函数中的 0 也可以换成null（空置）

结果：

注意：

使用decode函数时，作为判断条件的数据格式应与表中数据格式保持一致。

例如在这个选课表中，kno 的数据格式是 char(4) ，但我输入的数据是三位数，

在输入数据后，数据库会在三位数之后补上一位空格。

decode函数探索

如果不用sum函数会怎么样呢？

去除sum函数：

SELECT sname,decode(kno,'001 ',grade,0) 高数,decode(kno,'002 ',grade,0) 马原,decode(kno,'003 ',grade,0) 数据库,decode(kno,'004 ',grade,0) 体育,decode(kno,'005 ',grade,0) 英语from xuesheng,xuankewhere xuesheng.sno = xuanke.snoGROUP BY snameORDER BY sname;

运行结果：

不用sum ，group by语句将会出错

我们先运行查询部分函数

运行结果:

可以看到每科成绩多出了4个0，我们很容易就联想到在decode函数中的 ‘0’ ，多出了四个0值，如果我们将 0 换成 null 呢？

代码：

SELECT sname,decode(kno,'001 ',grade,null) 高数,decode(kno,'002 ',grade,null) 马原,decode(kno,'003 ',grade,null) 数据库,decode(kno,'004 ',grade,null) 体育,decode(kno,'005 ',grade,null) 英语from xuesheng,xuankewhere xuesheng.sno = xuanke.snoGROUP BY snameORDER BY sname;

运行结果

单独运行查询部分

结论：

1、group by应该和聚集函数一同使用。

2、sum可以起到数据压缩的作用，配合group by可以将decode产生的多于数据去除，得到我们需要的数据

case when

case when end编写和维护较麻烦，但是适合的场景较多

用课程名做判定条件

SELECT sname,max(case when kname = '高数' then grade else null end) 高数,max(case when kname = '马原' then grade else null end) 马原,max(case when kname = '数据库' then grade else null end) 数据库,max(case when kname = '体育' then grade else null end) 体育,max(case when kname = '英语' then grade else null end) 英语from xuesheng,kecheng,xuankewhere xuesheng.sno = xuanke.sno and kecheng.kno = xuanke.kno--三表链接查询GROUP BY snameORDER BY sname;--null可以换成0

结果

case when探索

去除max等操作与decode中的操作一致，结果也一样

pivot

格式

SELECT ....FROM <table-expr>PIVOT(aggregate-function(<column>)-- 聚集函数FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)-- 列名转换) AS <alias>WHERE .....

代码

select * from(select a.sno,a.sname,c.kname,b.grade -- 查询学号，学生姓名，课程名称，成绩from xuesheng a,xuanke b,kecheng c-- 从学生表，选课表，课程表where a.sno = b.sno and b.kno = c.kno -- 三表连接查询，两个条件：学生表中的学号等于选课表中的学号，选课表中的课程号等于课程表中的课程号order by a.sno,a.sname -- 依照学号，学生姓名排列)pivot( max(grade) for kname in( -- 即要转成列的字段'高数' as 高数, -- max(grade) 此处必须为聚合函数, sum(grade)也可以'马原' as 马原, -- in () 对要转成列的每一个值指定一个列名'数据库' as 数据库,-- ''之中是判断的条件，as之后是查询之后显示的列名（别名）'体育' as 体育,'英语' as 英语))where 1=1 --查询条件亦可以没有order by sno,sname; --依照学号、学生姓名排序

结果