SQL语句优化,帮忙看看,谢谢
语句本身没有问题,关键是能否进行优化?找个高手帮忙看看.
这条命令是出学生成绩的,当中用到了大量的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
[解决办法]
这sql 看着头疼啊 是表没设计好？ 最好格式也可以整理下
[解决办法]
- SQL code
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
[解决办法]
GOD
实在有些长
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
无非就是想实现如下效果：
select regexp_substr('1001,1002,1003,1004,1005', '[^,]+', 1, level) as line from dual
connect by level <= 5
将字段信息由一行转为多行。
LINE
------------------------
1001
1002
1003
1004
1005
--用下面的例子试试，没有楼主的具体数据，我直接用例子了，是网上抄的。
--原始结构
C1 C2 C3
------ ------------------- -------------------------
张三 胸外科,皮肤科 2000-11-19 00:00:00
李四 胸外科 2001-01-04 00:00:00
王五 妇产科,骨科 2001-01-08 00:00:00
--单行字段拆分为多行
with t1 as
(
select '张三' c1,'胸外科,皮肤科' c2,date'2000-11-19' c3 from dual
union all
select '李四','胸外科',date'2001-01-04' from dual
union all
select '王五','妇产科,骨科',date'2001-01-08' from dual
)
select c1,
substr(','||c2||',',instr(','||c2,',',1,b.rn)+1,
instr(c2||',',',',1,b.rn)-instr(','||c2,',',1,b.rn)) c2,c3
from t1,
(select rownum rn from t1
--connect by rownum<10
connect by rownum < nvl(length(regexp_replace(c2,'[^,]')),0)
) b
where length(c2)-length(replace(c2,','))+1>=b.rn
order by c1,b.rn
--查询结果
C1 C2 C3
------ --------------------- -------------------------
张三 胸外科 2000-11-19 00:00:00
张三 皮肤科 2000-11-19 00:00:00
李四 胸外科 2001-01-04 00:00:00
王五 妇产科 2001-01-08 00:00:00
王五 骨科 2001-01-08 00:00:00
上面的a1至a7，可以改为一句，至于后面的23个字段，标准的行转列
ORACLE提供了povit函数进行转换。
我就不重复写了。
[解决办法]
太长了，不容易维护，建议调整思路
[解决办法]
子查询里的order by 可以去了。