Oracle相关的几道题目,请高手给予解答

发布网友 发布时间:2022-04-23 14:58

我来回答

2个回答

热心网友 时间:2022-05-04 15:19

第一个问题:
SQL>Select distinct(courseid) as 课程ID,course as 课程名称,
count(case when grade between 85 and 100 then 1 else null end) over(partition by courseid) as "[85-100]",
count(case when grade between 70 and 84 then 1 else null end) over(partition by courseid) as "[70-84]" ,
count(case when grade between 60 and 69 then 1 else null end) over(partition by courseid) as "[60-69]" ,
count(case when grade<60 then 1 else null end) over(partition by courseid) as "[<60]"
from training order by courseid;
结果:
课程ID 课程名称 [85-100] [70-84] [60-69] [<60]
---------- ---------- ---------- ---------- ---------- ----------
1 T-SQL 0 0 1 1
2 Java 0 2 0 2
3 Oracle 1 2 0 1
第二个问题:
可以先建一张临时表,存放你上面那条SQL语句的结果,然后再用update更新training的orders列。
SQL> create table train_temp(courseid int,eid varchar(5),mark int);
SQL> insert into train_temp(courseid,eid,mark) select courseid,eid,mark from (
select courseid,eid,DENSE_RANK() OVER(PARTITION BY course ORDER BY grade DESC) mark from training);
SQL>Update training a
set orders=(select mark from train_temp b where a.courseid=b.courseid and a.eid=b.eid);

感觉做的有点复杂,不知道有没有更简单的方法。

热心网友 时间:2022-05-04 16:37

首先你建表就不严谨,没有外键关联 不符合设计原则 实际应用中会引发很多问题

根据你的题目显示 外键应该是 training表的 eid varchar(5) 字段上

第一题答案:
select a.courseid as 课程ID,
a.course as 课程名字,
(select count(1) from training b where b.grade between 85 and 100) as "[100 - 85]",
(select count(1) from training b where b.grade between 47 and 84) as "[47 - 84]",
(select count(1) from training b where b.grade between 60 and 69) as "[60 - 69]",
(select count(1) from training b where b.grade < 60) as "[< 60]"
from training a

声明声明:本网页内容为用户发布,旨在传播知识,不代表本网认同其观点,若有侵权等问题请及时与本网联系,我们将在第一时间删除处理。E-MAIL:11247931@qq.com