Sno Cno Degree
103 3-245 86
105 3-245 75
109 3-245 68
103 3-105 92
105 3-105 88
109 3-105 76
101 3-105 64
107 3-105 91
108 3-105 78
101 6-166 85
107 6-166 79
108 6-166 81
low uup rank
90 100 A
80 89 B
70 79 C
60 69 D
0 59 E
一张是学生成绩表,一张是成绩等级表
请问如何将这两张表连接,输出学生编号和成绩等级?
103 3-245 86
105 3-245 75
109 3-245 68
103 3-105 92
105 3-105 88
109 3-105 76
101 3-105 64
107 3-105 91
108 3-105 78
101 6-166 85
107 6-166 79
108 6-166 81
low uup rank
90 100 A
80 89 B
70 79 C
60 69 D
0 59 E
一张是学生成绩表,一张是成绩等级表
请问如何将这两张表连接,输出学生编号和成绩等级?
解决方案 »
- 这个触发器要怎写呢!!求高手
- TRS5.2、Lucene3.1、oracle11.2g的全文、天宇的全文索引功能比较
- 写一个存储过程 输入学生编号,把学生的所有课程、成绩打印出来
- Oracle角色和权限的关系??
- 高分求一存储过程
- oracle结果显示
- oracle存储过程编译不了了,怎样处理?
- 我需要一个复杂的ORACLE查询语句,请高手帮助我
- 求高手写一个oracle触发器,在线等!
- 在线等:select count(*) from a where 'S'='S',当表a为空时,如何能让返回值大于0啊
- oracle listener.ora tnsnames.ora sqlnet.ora 问题
- oracle 完整的 存储过程 触发器 视图 的简单例子
create table stu(
Sno number,
Cno varchar2(10),
Degree number
);insert into stu(sno,cno,degree)values(103,'3-245',86);
insert into stu(sno,cno,degree)values(105,'3-245',75);
insert into stu(sno,cno,degree)values(109,'3-245',68);
insert into stu(sno,cno,degree)values(103,'3-105',92);
insert into stu(sno,cno,degree)values(105,'3-105',88);
insert into stu(sno,cno,degree)values(109,'3-105',76);
insert into stu(sno,cno,degree)values(101,'3-105',64);
insert into stu(sno,cno,degree)values(107,'3-105',91);
insert into stu(sno,cno,degree)values(108,'3-105',78);
insert into stu(sno,cno,degree)values(101,'6-166',85);
insert into stu(sno,cno,degree)values(107,'6-166',79);
insert into stu(sno,cno,degree)values(108,'6-166',81); create table rk (
low number,
uup number,
rank varchar2(1)
);
insert into rk (low,uup,rank)values(90,100,'A');
insert into rk (low,uup,rank)values(80,89 ,'B');
insert into rk (low,uup,rank)values(70,79 ,'C');
insert into rk (low,uup,rank)values(60,69 ,'D');
insert into rk (low,uup,rank)values(0 ,59 ,'E'); select a.sno,a.cno,a.degree,
(select b.rank from rk b where a.degree between b.low and b.uup) as rank
from stu a;
这种需求一般不用单独建表,而是直接在SQL中如下处理:select sno,cno,degree,
case
when degree between 90 and 100 then 'A'
when degree between 80 and 89 then 'B'
when degree between 70 and 79 then 'C'
when degree between 60 and 69 then 'D'
else 'E'
end as rank
from stu;
2 cno,
3 case
4 when degree >= 90 and degree <= 100 then
5 'A'
6 when degree >= 80 and degree <= 89 then
7 'B'
8 when degree >= 70 and degree <= 79 then
9 'C'
10 when degree >= 60 and degree <= 69 then
11 'D'
12 when degree >= 0 and degree <= 59 then
13 'E'
14 end rank
15 from s
16 order by sno
17 ; SNO CNO RANK
---------- ---------- ----
101 3-105 A
103 3-105 A
103 3-245 B
105 3-245 C
105 3-105 B
107 3-105 D
108 3-105 C
109 3-105 C
109 3-245 D9 rows selected
b.rank from stu a, rk b where a.degree between b.low and b.uup;