/*
*自动做题
*
*/
--实际成绩
select  cj.KSBH, cj.KSLB, fs,200-cj.FS*2 kf from (
select  KSBH, KSLB, FS from (
select  double (substr(clh,POSSTR(clh,‘:‘)+1,POSSTR(clh,‘;‘)-POSSTR(clh,‘:‘)-1)) fs,clh, b.RYSQID, b.KSBH, b.XM, b.KSLBBH, b.KSLB, b.BY09, b.KSCJ
from (
select  substr(BY09, WZ,length(BY09)-wz+1) clh,a.RYSQID, a.KSBH, a.XM, a.KSLBBH, a.KSLB, a.BY09, a.KSCJ
from (
select ksxx.RYSQID,ksxx.KSBH , ksxx.XM, ksxx.KSLBBH, kslb.KSLBBH kslb ,case when RIGHT(by09,1)<>‘;‘ then by09||‘;‘ else by09 end by09, ksxx.KSCJ , LOCATE (kslb.KSLBBH,by09) wz
from KS_KSXX ksxx
left join KS_KSLB kslb on LOCATE (kslb.KSLBBH, ksxx.kslbbh)  >0 and kslb.KSLBBH<>‘404‘
where ksxx.KSBPBH>‘330416081‘ and  ksxx.KSBPBH like ‘3304%‘ and ksxx.REMOVED=‘0‘
and  ksxx.KSCJ is not null and  kslb.KSLBBH like ‘%40%‘
and ksxx.BY09 is not null
)a
)b
)c
union all
select ksxx.KSBH , kslb.KSLBBH kslb ,double(ksxx.KSCJ) fs
from KS_KSXX ksxx
left join KS_KSLB kslb on LOCATE (kslb.KSLBBH, ksxx.kslbbh)  >0 and kslb.KSLBBH<>‘404‘
where ksxx.KSBPBH>‘330416081‘ and  ksxx.KSBPBH like ‘3304%‘ and ksxx.REMOVED=‘0‘
and kslb.KSLBBH not like ‘4%‘
) cj;
--考题规则

    select  STLBBH, sum(TS) ts, MTFS, KSLBBH
    from KS_STGZ
    group by STLBBH, MTFS, KSLBBH

自动做题