[oracle/sql]写SQL从学生考试成绩三表中选出五门分综合超过720的尖子
任务：有学生，科目，考分三张表，需要从中筛选出五门考分总和超过720的学生。
科目表最简单只有五条记录：
CREATE TABLE tb_course ( id NUMBER not null primary key, name NVARCHAR2(10) not null ) Insert into tb_course(id,name) values('1','语文'); Insert into tb_course(id,name) values('2','数学'); Insert into tb_course(id,name) values('3','英语'); Insert into tb_course(id,name) values('4','物理'); Insert into tb_course(id,name) values('5','化学');
学生表只有两个字段，但数据量却有十万：
CREATE TABLE tb_student ( id NUMBER not null primary key, name NVARCHAR2(20) not null ) Insert into tb_student select rownum,dbms_random.string('*',dbms_random.value(15,20)) from dual connect by level<=100000 order by dbms_random.random
成绩表很庞大，需要在其中创建共两百万条数据：
CREATE TABLE tb_score ( id NUMBER not null primary key, studentid int not null, courseid int not null, score int not null )
Insert into tb_score
select rownum,dbms_random.value(0,100000),dbms_random.value(1,5),dbms_random.value(0,150) from dual
connect by level<=2000000
order by dbms_random.random
执行完毕以后commit.
现在可否开始查询呢？还不行，我们需要把每个考生单科最高分数据遴选出来，放到一张新表中。
create table tb_score2 as select * from tb_score where (studentid,courseid,score) in( select studentid,courseid,max(score) as score from tb_score group by studentid,courseid)
在我实验中，新表中约有49万数据，是原表的四分之一。
那么为什么不删除tb_score表中不是每个考生单科最高分那些记录呢，比如用下来的sql去删：
delete from tb_score where id not in( select id from tb_score where (studentid,courseid,score) not in( select studentid,courseid,max(score) as score from tb_score group by studentid,courseid) )
是因为这样删除实在是太慢了，我浏览了百度新闻，51job首页和电影港首页居然还没完，选出数据放到新表会快得多。
然后用以下语句就能找出五门总分在720以上的学生：
select stu.id,stu.name,sco.sumscore from tb_student stu, (select new_score.studentid,new_score.sumscore,new_score.cids from (select studentid,sum(score) as sumscore,(listagg(courseid,',') within group (order by courseid)) as cids from tb_score2 group by studentid ) new_score where new_score.cids=(select listagg(id,',') within group (order by id) from tb_course) and new_score.sumscore>720 ) sco where stu.id=sco.studentid order by sco.sumscore desc
让我们看看这些英才都是哪些人：
SQL> select stu.id,stu.name,sco.sumscore from tb_student stu, 2 (select new_score.studentid,new_score.sumscore,new_score.cids from 3 (select studentid,sum(score) as sumscore,(listagg(courseid,',') within group (order by courseid)) as cids from tb_score2 4 group by studentid ) new_score where new_score.cids=(select listagg(id,',') within group (order by id) from tb_course) 5 and new_score.sumscore>720 ) sco 6 where stu.id=sco.studentid 7 order by sco.sumscore desc; ID NAME SUMSCORE ---------- ---------------------------------------- ---------- 60230 MGHSGQWZZLWTMHAFCK 738 98913 FCIJHDBJDKUCLFTIOLR 737 19881 OWWHEDLATQLSUVXF 736 8711 UOAOGXWBATDMYBFM 736 26842 MUVPTHSTPNSKWSXIFAPP 735 58112 GOGZBZKYIZQBAYQS 735 42644 ZCWWQFSGOZMDFMS 735 42177 XDRUJHTZTOJVSFGI 734 56021 FBYUWGMGZORIQBXLTHO 733 65292 HUOUBIIWJNLAOGZW 733 87009 EBMPZNHSZMXMTSUC 732 ID NAME SUMSCORE ---------- ---------------------------------------- ---------- 83641 VKEKWTDNFDHXZRRPWIYD 731 58593 ZECIYJCZNYMUSNTG 731 32116 PZXWQZXAYLVAWNYG 729 97616 FGQGNKHTPDHHWOUU 729 74935 QNEHWSJTFLOSMXB 729 66747 WJURFLHNXQFDKNNOVT 729 49933 WGGGACVVJXXCODNLLB 729 23554 JKUNYBSSEKXQCVWE 728 55634 TCLLHZESREMRLIIWRUK 727 9387 EMYCJDHYLAXSGKE 726 93450 KQSBMYGSCRETYKYSYK 726 ID NAME SUMSCORE ---------- ---------------------------------------- ---------- 76314 NHOPFXSAYNKHSKHZJNY 726 76186 TXVXNSZETKCGLLX 726 61658 KSVTBNNAAJWZVVHP 726 33200 KJZXFGIDCJRRNKWNKG 726 84699 TDWMXMJRJTFCLGTC 726 86265 HBMIDMWVXMESKIIIQFPV 726 63956 MGPVABXLFQVKMKOQHN 725 33717 ABVGWFOAMIEQGIXIHBS 725 96610 OYOIJZGDXHWHTFNUDL 725 13104 KXCWLAUGOZSKQWTL 724 91912 MJYPLBSMWPEOLXUGGD 724 ID NAME SUMSCORE ---------- ---------------------------------------- ---------- 83508 IBNWASUMLVNLBXCURX 724 50997 YETMKRMKNAYMQQORGB 724 33868 WBXWDFIALHEAPPHKRAM 724 20391 RDHGEGGOIGVWTNJMJ 724 70595 SMVMJFVMDIYUNWYXC 724 8865 EYZZZGOAJNUENIXT 723 73241 DYMNRSECWZYLWFI 723 60378 YYQYSKIVBITGICD 723 13578 TLGLDLAYPRRCFTWQKRX 723 27338 HPAMAIQHHQNKDYJ 723 41174 HPMAFEVRCXEBVRRVEBYI 723 ID NAME SUMSCORE ---------- ---------------------------------------- ---------- 93860 VVXTQWGQGPEALAN 723 89181 AUAVRUBGTVEQISOBAFP 723 87519 MFOWKQSBTJVRJXX 723 83007 OZOYETVCZMJNXSIOSX 723 85947 DXSJSSMEMCJAIIGT 722 52919 FXELVCEHUAUMPHRKONX 722 94309 NTOCAERVSKNGLEZNIZ 722 34681 ZYOXMRWYYOVWONLNDR 722 30518 JNPYQNNWBPSKICREMPE 722 49635 MTJFGEKFQREYUQWPEWF 722 80760 LLHVLFAKXVSHTVYHOC 722 ID NAME SUMSCORE ---------- ---------------------------------------- ---------- 57707 SLYATRQZDXZYFVEM 722 18030 QZMPAWQKYISWWTSWM 722 74491 JLKIFEQCCLFCFIEFW 722 43296 VBLZPERIAXJYYYSTI 722 80816 IQSGRNLVHELBTSLGZ 722 92947 JHXVIRBODOUDZMMWP 722 5212 LCSFRFCWAFTLFPBWLU 721 9299 RUTLPLEFYYDVKLHPLK 721 10058 VBPVPMMAXRZIZLXHEI 721 10361 GBIXPYTHTZZWDPKW 721 15331 UMJLKKQHAKKBAOH 721 ID NAME SUMSCORE ---------- ---------------------------------------- ---------- 28393 LDLWTKPQSXLRWYN 721 39285 RIIBDINLLFXHETSPA 721 46517 TNUZAIUCZKWBFAGWEV 721 58569 ZINHWFNOOTQPEQOA 721 60567 UDECJDCGZIZZWUM 721 61563 SXBXGEBVXVNKSCWTUHLW 721 66670 XZYWNVSUWZWRYVYTVD 721 68668 FSAKVTSSBMYFPOSPSLG 721 91083 MGZJEJCGBSXEANPBHZK 721 92262 QKMPVBTYMXUVOGSAL 721 95418 QZQFIHRMNIGCDXSYHE 721 ID NAME SUMSCORE ---------- ---------------------------------------- ---------- 99418 HMKXSDFBGRETSZXYX 721 已选择78行。
2020年1月23日
