高分求SQL优化问题

Question

有以下表结构： 成绩表(CJB)结构 列名                          可空值否  类型              备注 
------------------------------- -------- ---- 
KCH                            NOT NULL VARCHAR2(10)      课程号 
KCM                                      VARCHAR2(50)      课程名 
KXH                                      NUMBER(4)          课序号 
XH                              NOT NULL VARCHAR2(12)      学号 
XNXQ                                    VARCHAR2(11)      学年学期 
XF                                      NUMBER(5,1)        学分 
XS                                      NUMBER(4,1)        学时数 
KCSX                                    VARCHAR2(4)        课程属性 
KSCJ                                    NUMBER(5,1)        考试成绩 
CXBKBZ                                  VARCHAR2(4)    成绩补考备注(补考,重修,辅修) ... 学籍表(XJB)结构: 列名                          可空值否  类型            备注 
------------------------------- -------- ---- 
XH                              NOT NULL VARCHAR2(12)    学号 
XM                              NOT NULL VARCHAR2(40)    姓名 
XB                                      VARCHAR2(2)    性别 
XSH                                      VARCHAR2(5)    系所号 
ZYH                                      VARCHAR2(8)    专业号 
ZYFX                                    VARCHAR2(30)    专业方向（专业名） 
BM                                      VARCHAR2(10)    班级名称 
RXNJ                                    VARCHAR2(4)    入学年级 
SSNJ                                    VARCHAR2(4)    所属年级 
PYFS                                    VARCHAR2(20)    培养方式(层次) 
... 系所表(XSB)结构: 列名                          可空值否  类型            备注 
------------------------------- -------- ---- 
XSH                            NOT NULL VARCHAR2(5)      系所号 
XSM                                      VARCHAR2(30)    系所名称 学分绩点转化表(XFJ)结构: 列名                          可空值否  类型 
------------------------------- -------- ---- 
JDH                            NOT NULL NUMBER(2) 
JDS                                      NUMBER(2,1) 
XXF                                      NUMBER(5,1) 
SXF                                      NUMBER(5,1) 
BZ                                      VARCHAR2(20)  
以下是XFJ内的数据(各课程成绩通过这张表转换): JDH      JDS      XXF      SXF BZ 
--- --------- --------- --------- -------------- 
  1        4        90      100 A（优） 
  2      3.7        87      89.9 A－ 
  3      3.3        84      86.9 B＋ 
  4        3        81      83.9 B（良） 
  5      2.7        78      80.9 B－ 
  6      2.3        75      77.9 C＋ 
  7        2        72      74.9 C（中） 
  8      1.7        69      71.9 C－ 
  9      1.3        66      68.9 D＋ 
10        1        60      65.9 D 
12        0        0      59.9 F（不及格）        用sql语句生成如下的形式的报表： 
系别  学号  性别 专业  培养层次  必修课学分  限选课学分  任选课学分（通识、复合） 任选课学分（教育类） 重修课程门次 重修课程学分  平均学分绩点 
我的sql语句如下: 
select xsb.xsm as 系别,xjb.zyfx as 专业, 
      xjb.xh as 学号,xjb.xm as 姓名, 
      xjb.XB as 性别,xjb.bm as 班号, 
      xjb.PYFS as 培养方式, 
      sum(case 
      when x.kcsx='必修' and x.kscj>=60 
      then x.xf 
      else 0 
      end 
      ) as 必修课程学分, 
      sum(case 
      when x.kcsx='限选' and x.kscj>=60 
      then x.xf else 0 
      end 
      ) as 限选课学分, 
      sum(case 
      when (instr(x.kch,'G')!=0  or instr(x.kcm,'复合')!=0) and x.kcsx='任选' and x.kscj>=60 
      then x.xf 
      else 0 
      end 
      )  as 通识复合学分, 
      sum(case 
      when instr(x.kch,'JY')!=0 and x.kscj>=60 
      then x.xf else 0 
      end 
      ) as 教育类课学分, 
      sum(case 
      when x.kscj>=60 
      then x.xf 
      else 0 
      end 
      )  as 总计学分, 
      count(decode(x.cxbkbz,'补考',1,null)) as 补考课程门次, 
      count(decode(x.cxbkbz,'重修',1,null)) as 重修课程门次, 
      sum(decode(x.cxbkbz,'重修',x.xf,0)) as 重修学分数, 
      sum(x.jds*xf) as 总学分绩点 from jw.code_xsb ,jw.xj_xjb,    
      (select a.xh,a.kch,a.kcm,a.kcsx,a.cxbkbz,a.kscj,a.xf,max(b.JDS) as JDS 
      from cjb a,xfj b 
      where a.kscj>  =b.xxf and a.kscj  <=b.sxf 
            and a.kssj=( 
            select max(kssj) from cjb where xh=a.xh and kch=a.kch) 
            group by 
            a.xh,a.kch,a.kcm,a.kcsx,a.cxbkbz,a.kscj,a.xf) x  
            where xsb.xsh = xjb.xsh and xjb.xh = x.xh 
                  and xjb.ssnj='2005' 
            group by xsb.xsm ,xjb.xh,xjb.xm,xjb.xb , 
            xjb.zyfx,xjb.bm,xjb.pyfs 我选出的记录只有3000多条，但执行时间需要花6分钟，请问如何优化呢？

Answer 1

when (instr(x.kch,'G')!=0  or instr(x.kcm,'复合')!=0) and x.kcsx='任选' and x.kscj>=60----这里的instr看能否转换成别的实现方法 
      then x.xf 
      else 0 
      end 
      )  as 通识复合学分, 
      sum(case 
      when instr(x.kch,'JY')!=0 and x.kscj>=60 select a.xh,a.kch,a.kcm,a.kcsx,a.cxbkbz,a.kscj,a.xf,max(b.JDS) over (partition by a.xh,a.kch,a.kcm,a.kcsx,a.cxbkbz,a.kscj,a.xf)/*max(b.JDS) as JDS*/
      from cjb a,xfj b 
      where a.kscj>  =b.xxf and a.kscj  <=b.sxf 
            and a.kssj=( 
            select max(kssj) from cjb where xh=a.xh and kch=a.kch) 
            /*group by 
            a.xh,a.kch,a.kcm,a.kcsx,a.cxbkbz,a.kscj,a.xf*/

Answer 2

为什么大家总喜欢在一个sql语句里面解决所有的问题呢？

Answer 3

1、从表结构上着手，简化表结构
2、使用联合查询 inner join on