1 /* 2 *自动做题 3 * 4 */ 5 --实际成绩 6 select cj.KSBH, cj.KSLB, fs,200-cj.FS*2 kf from ( 7 select KSBH, KSLB, FS from ( 8 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 9 from ( 10 select substr(BY09, WZ,length(BY09)-wz+1) clh,a.RYSQID, a.KSBH, a.XM, a.KSLBBH, a.KSLB, a.BY09, a.KSCJ 11 from ( 12 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 13 from KS_KSXX ksxx 14 left join KS_KSLB kslb on LOCATE (kslb.KSLBBH, ksxx.kslbbh) >0 and kslb.KSLBBH<>'404' 15 where ksxx.KSBPBH>'330416081' and ksxx.KSBPBH like '3304%' and ksxx.REMOVED='0' 16 and ksxx.KSCJ is not null and kslb.KSLBBH like '%40%' 17 and ksxx.BY09 is not null 18 )a 19 )b 20 )c 21 union all 22 select ksxx.KSBH , kslb.KSLBBH kslb ,double(ksxx.KSCJ) fs 23 from KS_KSXX ksxx 24 left join KS_KSLB kslb on LOCATE (kslb.KSLBBH, ksxx.kslbbh) >0 and kslb.KSLBBH<>'404' 25 where ksxx.KSBPBH>'330416081' and ksxx.KSBPBH like '3304%' and ksxx.REMOVED='0' 26 and kslb.KSLBBH not like '4%' 27 ) cj; 28 --试题规则 29 select STLBBH, sum(TS) ts, MTFS, KSLBBH 30 from KS_STGZ 31 where KSLBBH='501' 32 group by STLBBH, MTFS, KSLBBH; 33 --试题规则 34 select kslb.KSLBBH , gz1.TS ts1, gz1.MTFS fs1 , gz2.TS ts2, gz2.MTFS fs2, gz3.TS ts3, gz3.MTFS fs3 35 from KS_KSLB kslb 36 left join (select STLBBH, sum(TS) ts, MTFS, KSLBBH from KS_STGZ group by STLBBH, MTFS, KSLBBH) gz1 37 on gz1.STLBBH='102201' and kslb.KSLBBH = gz1.KSLBBH 38 left join (select STLBBH, sum(TS) ts, MTFS, KSLBBH from KS_STGZ group by STLBBH, MTFS, KSLBBH)gz2 39 on gz2.STLBBH='102202' and kslb.KSLBBH = gz2.KSLBBH 40 left join (select STLBBH, sum(TS) ts, MTFS, KSLBBH from KS_STGZ group by STLBBH, MTFS, KSLBBH)gz3 41 on gz3.STLBBH='102203' and kslb.KSLBBH = gz3.KSLBBH 42 ; 43 --where kslb.KSLBBH in ('101','102','201','202','301','302','303','304','305','306','307','308','309','310') 44 --人分数和试题的直接关系; 45 select cj.KSBH, cj.KSLB, fs,200-cj.FS*2 kf, gz.TS1, gz.FS1, gz.TS2, gz.FS2, gz.TS3, gz.FS3 from ( 46 select KSBH, KSLB, FS from ( 47 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 48 from ( 49 select substr(BY09, WZ,length(BY09)-wz+1) clh,a.RYSQID, a.KSBH, a.XM, a.KSLBBH, a.KSLB, a.BY09, a.KSCJ 50 from ( 51 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 52 from KS_KSXX ksxx 53 left join KS_KSLB kslb on LOCATE (kslb.KSLBBH, ksxx.kslbbh) >0 and kslb.KSLBBH<>'404' 54 where ksxx.KSBPBH>'330416081' and ksxx.KSBPBH like '3304%' and ksxx.REMOVED='0' 55 and ksxx.KSCJ is not null and kslb.KSLBBH like '%40%' 56 and ksxx.BY09 is not null 57 )a 58 )b 59 )c 60 union all 61 select ksxx.KSBH , kslb.KSLBBH kslb ,double(ksxx.KSCJ) fs 62 from KS_KSXX ksxx 63 left join KS_KSLB kslb on LOCATE (kslb.KSLBBH, ksxx.kslbbh) >0 and kslb.KSLBBH<>'404' 64 where ksxx.KSBPBH>'330416081' and ksxx.KSBPBH like '3304%' and ksxx.REMOVED='0' 65 and kslb.KSLBBH not like '4%' 66 ) cj 67 left join 68 ( 69 select kslb.KSLBBH , gz1.TS ts1, gz1.MTFS fs1 , gz2.TS ts2, gz2.MTFS fs2, gz3.TS ts3, gz3.MTFS fs3 70 from KS_KSLB kslb 71 left join (select STLBBH, sum(TS) ts, MTFS, KSLBBH from KS_STGZ group by STLBBH, MTFS, KSLBBH) gz1 72 on gz1.STLBBH='102201' and kslb.KSLBBH = gz1.KSLBBH 73 left join (select STLBBH, sum(TS) ts, MTFS, KSLBBH from KS_STGZ group by STLBBH, MTFS, KSLBBH)gz2 74 on gz2.STLBBH='102202' and kslb.KSLBBH = gz2.KSLBBH 75 left join (select STLBBH, sum(TS) ts, MTFS, KSLBBH from KS_STGZ group by STLBBH, MTFS, KSLBBH)gz3 76 on gz3.STLBBH='102203' and kslb.KSLBBH = gz3.KSLBBH 77 ) gz on cj.KSLB=gz.KSLBBH; 78 --吧关系存入中间表 79 insert into ZDZT(KSBH, KSLB, FS, KF, TS1, FS1, TS2, FS2, TS3, FS3 ) 80 select cj.KSBH, cj.KSLB, fs,200-cj.FS*2 kf, gz.TS1, gz.FS1, gz.TS2, gz.FS2, gz.TS3, gz.FS3 from ( 81 select KSBH, KSLB, FS from ( 82 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 83 from ( 84 select substr(BY09, WZ,length(BY09)-wz+1) clh,a.RYSQID, a.KSBH, a.XM, a.KSLBBH, a.KSLB, a.BY09, a.KSCJ 85 from ( 86 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 87 from KS_KSXX ksxx 88 left join KS_KSLB kslb on LOCATE (kslb.KSLBBH, ksxx.kslbbh) >0 and kslb.KSLBBH<>'404' 89 where ksxx.KSBPBH>'330416081' and ksxx.KSBPBH like '3304%' and ksxx.REMOVED='0' 90 and ksxx.KSCJ is not null and kslb.KSLBBH like '%40%' 91 and ksxx.BY09 is not null 92 )a 93 )b 94 )c 95 union all 96 select ksxx.KSBH , kslb.KSLBBH kslb ,double(ksxx.KSCJ) fs 97 from KS_KSXX ksxx 98 left join KS_KSLB kslb on LOCATE (kslb.KSLBBH, ksxx.kslbbh) >0 and kslb.KSLBBH<>'404' 99 where ksxx.KSBPBH>'330416081' and ksxx.KSBPBH like '3304%' and ksxx.REMOVED='0' 100 and kslb.KSLBBH not like '4%' 101 ) cj 102 left join 103 ( 104 select kslb.KSLBBH , gz1.TS ts1, gz1.MTFS fs1 , gz2.TS ts2, gz2.MTFS fs2, gz3.TS ts3, gz3.MTFS fs3 105 from KS_KSLB kslb 106 left join (select STLBBH, sum(TS) ts, MTFS, KSLBBH from KS_STGZ group by STLBBH, MTFS, KSLBBH) gz1 107 on gz1.STLBBH='102201' and kslb.KSLBBH = gz1.KSLBBH 108 left join (select STLBBH, sum(TS) ts, MTFS, KSLBBH from KS_STGZ group by STLBBH, MTFS, KSLBBH)gz2 109 on gz2.STLBBH='102202' and kslb.KSLBBH = gz2.KSLBBH 110 left join (select STLBBH, sum(TS) ts, MTFS, KSLBBH from KS_STGZ group by STLBBH, MTFS, KSLBBH)gz3 111 on gz3.STLBBH='102203' and kslb.KSLBBH = gz3.KSLBBH 112 ) gz on cj.KSLB=gz.KSLBBH; 113 114 ---- 115 select a3.FALG, a3.KSBH, a3.KSLB, a3.FS, a3.KF, a3.TS1, a3.FS1, a3.TS2, a3.FS2, a3.TS3, a3.FS3 116 ,case when FALG is null then(case when c3*fs3+c2*fs2=kf then 0 when MOD (kf-c3*fs3+c2*fs2, fs1)=0 then (kf-c3*fs3+c2*fs2)/fs1 end) 117 else a3.c1 end as c1 118 , a3.C2, a3.C3 119 from ( 120 select a2.FALG, a2.KSBH, a2.KSLB, a2.FS, a2.KF, a2.TS1, a2.FS1, a2.TS2, a2.FS2, a2.TS3, a2.FS3 121 , a2.C1 122 ,case when FALG is null then (case when kf-TS3*c3<fs2 then 0 123 when kf-ts3*c3-ts2*c2>0 then ts2 124 else (kf-ts3*c3)/fs2 125 end) 126 else a2.c2 end c2 127 ,a2.C3 128 from 129 ( 130 select a1.FALG, a1.KSBH, a1.KSLB, a1.FS, a1.KF, a1.TS1, a1.FS1, a1.TS2, a1.FS2, a1.TS3, a1.FS3 131 ,case when FALG=1 then a1.C1 when falg =2 then (kf-(c2*fs2))/fs1 when falg=1 then C1 end c1 132 ,case when FALG =2 then c2 when falg =1 then 0 end c2 133 ,case when FALG is null then(case when FS3*ts3< kf then TS3 else kf/fs3 end) else 0 end c3 134 from 135 ( 136 select case when FALG is null and mod((kf-c2*fs2),fs1)=0 and (kf-c2*fs2)<fs1*ts1 then 2 when falg is not null then falg end falg 137 , KSBH, KSLB, fs, KF, TS1, FS1, ts2,fs2,ts3,fs3,case when falg=1 then 0 else c2 end c2,case when falg=1 then a.KF/fs1 end c1 138 from ( 139 select case when TS1*fs1>=kf and mod(KF, FS1 )=0 then 1 end falg,case when fs2*ts2>kf then kf/fs2 else ts2 end c2 140 ,KSBH, KSLB, fs, KF, TS1, FS1, ts2,fs2,ts3,fs3 141 from ZDZT 142 )a 143 )a1 144 )a2 145 )a3