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
浙公网安备 33010602011771号