语句本身没有问题,关键是能否进行优化?找个高手帮忙看看.
这条命令是出学生成绩的,当中用到了大量的regexp_substr,这是从题块里面拆得分点,比如语文,如果有八个题块,就要拆八次,而每个题块又各自有不同的得分点,因此拆完之后可能要进行八次以上的合并,因为学生的考号是在另一张表里,进行拆分的表里面只有试卷的试卷号!
合并完之后,又还有一次合并,就是将得分点与客观题合并,所以这命令里面有X,Y表的命名.总体上,有三次大的合并!得分点本身的合并,是根据得分点的个数随机进行的,最多的一般是数学,可能有20多个得分点,就要并20多次,我个人感觉很有问题,效率低下...人数不多的时候,没什么问题,但一旦超过一万人......
我曾经弄好了一个程序,结果发现,在大考的情况下,一万多人的时候,这条命令有时候很慢!!!要三十秒,我受不了这个速度,能不能再快一点???
谢谢帮忙!
select x.code,x.name,x.schoolid,x.grade,x.km,x.classroomid,x.OMR1,x.OMR2,x.OMR3,x.OMR4,x.OMR5,x.OMR6,y.point1,y.point2,y.point3,y.point4,y.point5,y.point6,y.point7,y.point8,y.point9,y.point10,y.point11,y.point12,y.point13,y.point14,y.point15,y.point16,y.point17,y.point18,y.point19,y.point20,y.point21,y.point22,y.point23,x.subjectscore as SCORE from
(select a.code,a.name,a.schoolid,a.grade,a.km,a.classroomid,a.OMR1,a.OMR2,a.OMR3,a.OMR4,a.OMR5,a.OMR6,b.subjectscore from
(select * from newschool.cd_alldetail where examname=114 and km='初一语文') a,
(select * from cd_subjectscore_rank where examid=114 and subjectname='初一语文') b where a.code=b.code and a.km=b.subjectname order by code)x,
(select d.code,d.name,d.distid,d.schoolid,d.grade,d.classroomid,d.subjectid,E.point1,E.point2,E.point3,E.point4,E.point5,E.point6,E.point7,E.point8,E.point9,E.point10,E.point11,E.point12,E.point13,E.point14,E.point15,E.point16,E.point17,E.point18,E.point19,E.point20,E.point21,E.point22,E.point23 from
(select a.code,a.name,a.distid,a.schoolid,a.grade,a.classroomid,b.subjectid,b.paperid from
(select code,name,distid,schoolid,grade,classroomid from exam114.cd_student where subjectid=21) a,
(select code,subjectid,paperid from exam114.cd_scanlist where subjectid=21) b where a.code=b.code) d,
(select a1.paperid,a1.point1,a1.point2,a2.point3,a2.point4,a2.point5,a2.point6,a3.point7,a4.point8,a4.point9,a5.point10,a5.point11,a5.point12,a5.point13,a5.point14,a6.point15,a6.point16,a6.point17,a6.point18,a6.point19,a6.point20,a7.point21,a7.point22,a7.point23 from
(select subjectid,paperid,to_number(regexp_substr(subscore,'[^,]+',1,1)) point1,to_number(regexp_substr(subscore,'[^,]+',1,2)) point2 from exam114.cd_final where subjectid=21 and itemid=1) a1,
(select subjectid,paperid,to_number(regexp_substr(subscore,'[^,]+',1,1)) point3,to_number(regexp_substr(subscore,'[^,]+',1,2)) point4,to_number(regexp_substr(subscore,'[^,]+',1,3)) point5,to_number(regexp_substr(subscore,'[^,]+',1,4)) point6 from exam114.cd_final where subjectid=21 and itemid=2) a2,
(select subjectid,paperid,to_number(regexp_substr(subscore,'[^,]+',1,1)) point7 from exam114.cd_final where subjectid=21 and itemid=3) a3,
(select subjectid,paperid,to_number(regexp_substr(subscore,'[^,]+',1,1)) point8,to_number(regexp_substr(subscore,'[^,]+',1,2)) point9 from exam114.cd_final where subjectid=21 and itemid=4) a4,
(select subjectid,paperid,to_number(regexp_substr(subscore,'[^,]+',1,1)) point10,to_number(regexp_substr(subscore,'[^,]+',1,2)) point11,to_number(regexp_substr(subscore,'[^,]+',1,3)) point12,to_number(regexp_substr(subscore,'[^,]+',1,4)) point13,to_number(regexp_substr(subscore,'[^,]+',1,5)) point14 from exam114.cd_final where subjectid=21 and itemid=5) a5,
(select subjectid,paperid,to_number(regexp_substr(subscore,'[^,]+',1,1)) point15,to_number(regexp_substr(subscore,'[^,]+',1,2)) point16,to_number(regexp_substr(subscore,'[^,]+',1,3)) point17,to_number(regexp_substr(subscore,'[^,]+',1,4)) point18,to_number(regexp_substr(subscore,'[^,]+',1,5)) point19,to_number(regexp_substr(subscore,'[^,]+',1,6)) point20 from exam114.cd_final where subjectid=21 and itemid=6) a6,
(select subjectid,paperid,to_number(regexp_substr(subscore,'[^,]+',1,1)) point21,to_number(regexp_substr(subscore,'[^,]+',1,2)) point22,to_number(regexp_substr(subscore,'[^,]+',1,3)) point23 from exam114.cd_final where subjectid=21 and itemid=7) a7 where a1.paperid=a2.paperid and a1.paperid=a3.paperid and a1.paperid=a4.paperid and a1.paperid=a5.paperid and a1.paperid=a6.paperid and a1.paperid=a7.paperid order by a1.paperid) E where d.paperid=e.paperid and d.subjectid=21 order by d.code) y where x.code=y.code and x.name=y.name order by x.code