create table t_test_stu (id varchar2(32),classid varchar2(32)，score number); --id:学号 classid：年级id score:分数
create table t_test_stuname(id varchar2(32),name varchar2(32)); --id:学号 name：姓名
create table t_test_classname(classid varchar2(32),chassname varchar2(32)); --年级id chassname：年级名
insert into t_test_stu values ('1','0101',99);
insert into t_test_stu values ('2','0101',101);
insert into t_test_stu values ('3','0101',97);
insert into t_test_stu values ('4','0101',98);
insert into t_test_stu values ('5','0101',120);
insert into t_test_stu values ('6','0102',110);
insert into t_test_stu values ('7','0102',120);
insert into t_test_stu values ('8','0102',130);
insert into t_test_stu values ('9','0102',100);
insert into t_test_stu values ('10','0102',80);
insert into t_test_stuname values ('1','zhang1');
insert into t_test_stuname values ('2','zhang2');
insert into t_test_stuname values ('3','zhang3');
insert into t_test_stuname values ('4','zhang4');
insert into t_test_stuname values ('5','zhang5');
insert into t_test_stuname values ('6','zhang6');
insert into t_test_stuname values ('7','zhang7');
insert into t_test_stuname values ('8','zhang8');
insert into t_test_stuname values ('9','zhang9');
insert into t_test_stuname values ('10','zhang10');
insert into t_test_classname values ('0101','Grade1');
insert into t_test_classname values ('0102','Grade2');
--求张三的班级和分数，展示班级，分数
select t_test_classname.classname,t_test_stu.score from t_test_classname,t_test_stu,t_test_stuname
where t_test_classname.classid=t_test_stu.classid and t_test_stuname.id=t_test_stu.id
and t_test_stuname.id='zhang3';
--求grade1 的所有学生的分数总和， 展示classname，学生名字，分数总和
select t_test_classname.chassname,t_test_stuname.name,sum(t_test_stu.score)分数总和
from t_test_classname,t_test_stu,t_test_stuname
where t_test_classname.classid=t_test_stu.classid and t_test_stuname.id=t_test_stu.id
and t_test_classname='grade1'
group by t_test_stuname.id
-----sum 中运算后需要分组
--求grade1 的学生分数从高到低排序 展示 classname，学生名字，分数
select t_test_classname.classname,t_test_stuname.name,t_test_stu.score
from t_test_stu,t_test_stuname,t_test_classname
where t_test_classname.classid=t_test_stu.classid and t_test_stuname.id=t_test_stu.id
and t_test_classname='grade1'
order by t_test_stu.score desc;
---所有学生的分数排名 展示 学生姓名 排名
select name,t_test_classname.classid,
rank() over (partition by t_test_classname.classid order by score desc)paiming
from t_test_stu,t_test_stuname,t_test_classname
where t_test_stuname.id=t_test_stu.id;
---所有学生的分数排名和分差 展示 学生姓名 排名 分差
（暂时没写）
---分数在 80+ 分段 90+分段 100+分段 110+分段 120 分段的占比
select px,round(count1/totalcount*100,2) as zhanbi from
(
select px,count1,sum(count1) over () as totalcount from
(
select px,count(1) as count1 from
(
select
case when t_test_stu.score>=80 and t_test_stu.score<=90 then '80+'
when t_test_stu.score>=90 and t_test_stu.score<=100 then '90+'
when t_test_stu.score>=100 and t_test_stu.score<=110 then '100+'
when t_test_stu.score>=110 and t_test_stu.score<=120 then '110+'
when t_test_stu.score=120 then '120'
else '0-80' end as px
from t_test_stu;
)t1
group by px;
)t2
)t3
网友评论