任务：有学生，科目，考分三张表，需要从中筛选出五门考分总和超过720的学生。
科目表最简单只有五条记录：
CREATE TABLE tb_course
(
id NUMBER not null primary key,
name NVARCHAR2(10) not null
) Insert into tb_course(id,name) values('','语文');
Insert into tb_course(id,name) values('','数学');
Insert into tb_course(id,name) values('','英语');
Insert into tb_course(id,name) values('','物理');
Insert into tb_course(id,name) values('','化学');
学生表只有两个字段，但数据量却有十万：
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日