Oracle |  三表联结查询（分析函数）

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