有两张表:表 1
STUDNAME COURSENAME CSCORE USCORE
--------------------------------
jack A 80 1
jack B 60 5
jack C 70 3
rose A 95 1
rose B 85 5
rose C 75 3
lucy A 45 1
lucy B 55 5
lucy C 50 3
lily A 25 1
lily B 15 5
lily C 10 3表2
姓名 A B C 总学分
--------------------------------
jack 80 60 70 9
rose 95 85 75 9
lucy 45 55 50 9
lily 25 15 10 91. 从表1获取数据,格式化输出为表2。
2. 从表2读取数据,格式化输出为表1。
PS: 脚本关键语句最好有注释
STUDNAME COURSENAME CSCORE USCORE
--------------------------------
jack A 80 1
jack B 60 5
jack C 70 3
rose A 95 1
rose B 85 5
rose C 75 3
lucy A 45 1
lucy B 55 5
lucy C 50 3
lily A 25 1
lily B 15 5
lily C 10 3表2
姓名 A B C 总学分
--------------------------------
jack 80 60 70 9
rose 95 85 75 9
lucy 45 55 50 9
lily 25 15 10 91. 从表1获取数据,格式化输出为表2。
2. 从表2读取数据,格式化输出为表1。
PS: 脚本关键语句最好有注释
create table t1 (STUDNAME varchar2(20), COURSENAME varchar2(10), CSCORE number(3), USCORE number(5));insert into t1 values ('jack','A',80,1);
insert into t1 values ('jack','B',60,5);
insert into t1 values ('jack','C',70,3);
insert into t1 values ('rose','A',95,1);
insert into t1 values ('rose','B',85,5);
insert into t1 values ('rose','C',75,3);
insert into t1 values ('lucy','A',45,1);
insert into t1 values ('lucy','B',55,5);
insert into t1 values ('lucy','C',50,3);
insert into t1 values ('lily','A',25,1);
insert into t1 values ('lily','B',15,5);
insert into t1 values ('lily','C',10,3);
select STUDNAME,
sum(decode(COURSENAME,'A',CSCORE,0)) A, --判断每行的科目和成绩 根据姓名合并多行各科成绩
sum(decode(COURSENAME,'B',CSCORE,0)) B,
sum(decode(COURSENAME,'C',CSCORE,0)) C,
sum(USCORE) USCORE
from t1
group by STUDNAME
STUDNAME A B C USCORE
-------------------------------------------------
1 rose 95 85 75 9
2 jack 80 60 70 9
3 lucy 45 55 50 9
4 lily 25 15 10 9