首页 > 数据库 > mysql教程 > Oracle—decode函数行转列

Oracle—decode函数行转列

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
发布: 2016-06-07 15:38:49
原创
1711 人浏览过

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

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

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


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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

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)

);

登录后复制

2、给三张表插入相应的数据

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

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);

登录后复制


插入后的数据结果如下:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

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

登录后复制

3、对数据进行行转列操作

1

2

3

4

5

6

7

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函数格式:decode(column,if1,then1,defalut)

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

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


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

1

2

3

4

5

6

7

学号       姓名                                                       数学         英语       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

登录后复制

以上是通过oracle的decode函数进行的行转列操作,初次写博文,多多指教!

相关标签:
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板