Oracle—decode函数行转列

文章说明：根据成绩表，对每个学生的成绩数据进项行转列操作，最后每行显示每个学生的各科成绩。 以下是行转列的各个步骤操作。 1、创建学生信息，课程，成绩三张表 CREATE TABLE TB_STUDENT( STUDENTID VARCHAR2(10) PRIMARY KEY, STUNAME VARCHAR2(50), SE

文章说明：根据成绩表，对每个学生的成绩数据进项行转列操作，最后每行显示每个学生的各科成绩。

以下是行转列的各个步骤操作。

1、创建学生信息，课程，成绩三张表

CREATE TABLE TB_STUDENT(
 STUDENTID VARCHAR2(10) PRIMARY KEY,
 STUNAME VARCHAR2(50),
 SEX VARCHAR2(8),
 AGE NUMBER(5) 
);

CREATE TABLE TB_COURSE(
 COURSEID VARCHAR2(10) PRIMARY KEY,
 COURSENAME VARCHAR2(10),
 TEACHER VARCHAR2(10)
);

CREATE TABLE TB_SCORE(
 SCOREID VARCHAR2(10) PRIMARY KEY,
 COURSEID VARCHAR2(10),
 STUDENTID VARCHAR2(10),
 GRADE NUMBER(10)
);

INSERT INTO TB_STUDENT(STUDENTID,STUNAME,SEX,AGE) VALUES('20141101','SIM','BOY',22);
INSERT INTO TB_STUDENT(STUDENTID,STUNAME,SEX,AGE) VALUES('20141102','MARY','GIRL',22);
INSERT INTO TB_STUDENT(STUDENTID,STUNAME,SEX,AGE) VALUES('20141103','SILLY','GIRL',22);
INSERT INTO TB_STUDENT(STUDENTID,STUNAME,SEX,AGE) VALUES('20141104','ALICE','GIRL',22);
INSERT INTO TB_STUDENT(STUDENTID,STUNAME,SEX,AGE) VALUES('20141105','SAM','BOY',22);

insert into tb_course(COURSEID,coursename,teacher) VALUES('1001','MATH','SA');
insert into tb_course(COURSEID,coursename,teacher) VALUES('1002','ENGLISH','ALICE');
insert into tb_course(COURSEID,coursename,teacher) VALUES('1003','JAVA','BB');

INSERT INTO TB_SCORE(SCOREID,COURSEID,STUDENTID,GRADE) VALUES('10001','1001','20141101',99);
INSERT INTO TB_SCORE(SCOREID,COURSEID,STUDENTID,GRADE) VALUES('10002','1001','20141102',88);
INSERT INTO TB_SCORE(SCOREID,COURSEID,STUDENTID,GRADE) VALUES('10003','1001','20141103',77);
INSERT INTO TB_SCORE(SCOREID,COURSEID,STUDENTID,GRADE) VALUES('10004','1001','20141104',98);
INSERT INTO TB_SCORE(SCOREID,COURSEID,STUDENTID,GRADE) VALUES('10005','1001','20141105',96);
INSERT INTO TB_SCORE(SCOREID,COURSEID,STUDENTID,GRADE) VALUES('10006','1002','20141101',99);
INSERT INTO TB_SCORE(SCOREID,COURSEID,STUDENTID,GRADE) VALUES('10007','1002','20141103',99);
INSERT INTO TB_SCORE(SCOREID,COURSEID,STUDENTID,GRADE) VALUES('10008','1002','20141104',99);
INSERT INTO TB_SCORE(SCOREID,COURSEID,STUDENTID,GRADE) VALUES('10009','1003','20141102',99);
INSERT INTO TB_SCORE(SCOREID,COURSEID,STUDENTID,GRADE) VALUES('10010','1003','20141105',99);

插入后的数据结果如下：

STUDENTID STUNAME SEX AGE
---------- -------------------------------------------------- -------- ------
20141101 SIM BOY 22
20141102 MARY GIRL 22
20141103 SILLY GIRL 22
20141104 ALICE GIRL 22
20141105 SAM BOY 22


COURSEID COURSENAME TEACHER
---------- ---------- ----------
1001 MATH SA
1002 ENGLISH ALICE
1003 JAVA BB


SCOREID COURSEID STUDENTID GRADE
---------- ---------- ---------- -----------
10001 1001 20041101 99
10002 1001 20041102 88
10003 1001 20041103 77
10004 1001 20041104 98
10005 1001 20041105 96
10006 1002 20041101 99
10007 1002 20041103 99
10008 1002 20041104 99
10009 1003 20041102 99
10010 1003 20041105 99

select g.studentid 学号,(select stu.stuname from tb_student stu where stu.studentid=g.studentid) AS 姓名,
sum(decode(g.courseid,'1001',grade,0)) 数学,
sum(decode(g.courseid,'1002',grade,0)) 英语,
sum(decode(g.courseid,'1003',grade,0)) java
from tb_score g
group by g.studentid
order by g.studentid;

decode函数格式：decode(column,if1,then1,defalut)

表示列如果满足if1的条件，就显示then1的内容，不满足就显示default的值！

sum(decode(...))是对decode返回的数据进行累加

4、通过上面的查询，最后显示的查询结果如下

学号 姓名 数学 英语 JAVA
---------- -------------------------------------------------- ---------- ---------- ----------
20141101 SIM 99 99 0
20141102 MARY 88 0 99
20141103 SILLY 77 99 0
20141104 ALICE 98 99 0
20141105 SAM 96 0 99