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
一张是学生成绩表,一张是成绩等级表
请问如何将这两张表连接,输出学生编号和成绩等级?
解决方案 »
- 发一个sql问题,
- 怎么将oracle数据库表的日期字段改为系统日期 ,在线等!!!!
- 问个sql语句写法的问题,请诸位帮忙
- 根据A表的字段去查找在B表中最近一条相同的字段的记录,这段SQL我运行起来好慢,大家帮忙看下怎么提高效率呢?
- 求一条SQL语句
- 高分在线等待!!!当场解决当场给分!
- 怎样在存储过程中使用同义词?
- 请教:用sql loader装数时出现内存错误,怎么办,急!!!
- Error while registering Oracle JDBC Diagnosability MBean.
- 递归查询 排序问题 求指教?
- 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;