# SQL 增加多一欄顯示不同卷別的滿分分數

1. ### Mr.Stone Expand Collapse

文章:
25
讚:
0
我在之前的帖裡，找到計算及格率的sql，寫法如下。我發覺這寫法是以50分為及格分數。但我們有些科的滿分是100，有些是200或300，這方式出的及格率未能參考。

1. 因比較急用，簡單起見，若以下code可以多一欄顯示每科卷的滿分分數（例︰科目滿分︰200），已可解決我部份問題，主要想看看有沒有一些班別是沒有人過一半分數的。
2. 下一步，可否代修改以下編碼，按每卷滿分的50%來計算及格率？

感謝。

Select
a.classcode '班別',
a.subj '科目',
a.subjcomp '科目分卷',
a.highest '最高分',
a.lowest '最低分',
a.avge '平均分',
a.pass '合格率'
from(
select
a.classcode 'classcode',
e.ch_des 'subj',
d.ch_des 'subjcomp',
max(c.sysscore) 'highest',
min(c.sysscore) 'lowest',
str(round(avg(c.sysscore), 2), 10, 2) 'avge',
str(round(sum((case when c.sysscore >= 50.0 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' 'pass'
from vw_stu_lateststudent a
join tb_asr_time b
on b.timeseq=?
join tb_asr_subjcomassessdata c
on a.suid=c.suid and a.stuid=c.stuid and a.schlvl=c.schlevel and a.schsess=c.schsession and
a.schyear=c.schyear and a.classlvl=c.classlevel and c.timeseq=b.timeseq
left outer join tb_hse_sbjcmp d
on a.suid=d.suid and c.subjcode=d.sbj_code and c.subjcompcode=d.code_id
left outer join tb_hse_common e
on a.suid=e.suid and c.subjcode=e.code_id and e.tb_id='SBJ'
where a.schyear=? and a.classlvl=?
group by a.classcode, c.subjcode, e.ch_des, d.ch_des
union all
select
a.classcode 'classcode',
e.ch_des 'subj',
null 'subjcomp',
max(c.sysscore) 'highest',
min(c.sysscore) 'lowest',
str(round(avg(c.sysscore), 2), 10, 2) 'avge',
str(round(sum((case when c.sysscore >= 50.0 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' 'pass'
from vw_stu_lateststudent a
join tb_asr_time b
on b.timeseq=?
join tb_asr_subjassessdata c
on a.suid=c.suid and a.stuid=c.stuid and a.schlvl=c.schlevel and a.schsess=c.schsession and
a.schyear=c.schyear and a.classlvl=c.classlevel and c.timeseq=b.timeseq
left outer join tb_hse_common e
on a.suid=e.suid and c.subjcode=e.code_id and e.tb_id='SBJ'
where a.schyear=? and a.classlvl=?
group by a.classcode, c.subjcode, e.ch_des) a
order by 1,2,3

#1 Mr.Stone, 2021-07-08
2. ### edb-catherinewschan Expand Collapse

文章:
211
讚:
0
你好，

我加插了FULLSCORE的資料，而計算方面改了用FULLSCORE的50%
你可以參考一下：

Select
a.classcode '班別',
a.subj '科目',
a.subjcomp '科目分卷',
a.fullscore '科目滿分',
a.highest '最高分',
a.lowest '最低分',
a.avge '平均分',
a.pass '合格率'
from(
select
a.classcode 'classcode',
e.ch_des 'subj',
d.ch_des 'subjcomp',
d1.fullscore,
max(c.sysscore) 'highest',
min(c.sysscore) 'lowest',
str(round(avg(c.sysscore), 2), 10, 2) 'avge',
str(round(sum((case when c.sysscore >= d1.fullscore*0.5 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' 'pass'
on b.timeseq= ?
on a.suid=c.suid and a.stuid=c.stuid and a.schlvl=c.schlevel and a.schsess=c.schsession and
a.schyear=c.schyear and a.classlvl=c.classlevel and c.timeseq=b.timeseq
left outer join wsadmin.tb_asr_clslvlsubjsetting d1 on c.suid=d1.suid and c.schlevel=d1.schlevel and c.schsession=d1.schsession
and c.classlevel=d1.classlevel and c.schyear=d1.schyear and d1.timeseq=c.timeseq and d1.subjcode=c.subjcode and d1.moi=c.moi

on a.suid=d.suid and c.subjcode=d.sbj_code and c.subjcompcode=d.code_id
on a.suid=e.suid and c.subjcode=e.code_id and e.tb_id='SBJ'
where a.schyear=? and a.classlvl=?
group by a.classcode, c.subjcode, e.ch_des, d.ch_des, d1.fullscore
union all
select
a.classcode 'classcode',
e.ch_des 'subj',
null 'subjcomp',
d1.fullscore,
max(c.sysscore) 'highest',
min(c.sysscore) 'lowest',
str(round(avg(c.sysscore), 2), 10, 2) 'avge',
str(round(sum((case when c.sysscore >= d1.fullscore*0.5 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' 'pass'
on b.timeseq= ?
on a.suid=c.suid and a.stuid=c.stuid and a.schlvl=c.schlevel and a.schsess=c.schsession and
a.schyear=c.schyear and a.classlvl=c.classlevel and c.timeseq=b.timeseq
left outer join wsadmin.tb_asr_clslvlsubjsetting d1 on c.suid=d1.suid and c.schlevel=d1.schlevel and c.schsession=d1.schsession
and c.classlevel=d1.classlevel and c.schyear=d1.schyear and d1.timeseq=c.timeseq and d1.subjcode=c.subjcode and d1.moi=c.moi

on a.suid=e.suid and c.subjcode=e.code_id and e.tb_id='SBJ'
where a.schyear=? and a.classlvl=?
group by a.classcode, c.subjcode, e.ch_des, d1.fullscore) a
order by 1,2,3

#2 edb-catherinewschan, 2021-07-09
Last edited: 2021-07-09
3. ### Mr.Stone Expand Collapse

文章:
25
讚:
0
顯示SQL錯誤呢（見附圖），無法執行。

#### 附件文件:

• ###### screenshot.1.png
文件大小:
2.2 KB
瀏覽:
17
#3 Mr.Stone, 2021-07-12
4. ### edb-catherinewschan Expand Collapse

文章:
211
讚:
0
試試以下的SQL，內容跟上面一樣...

Select
a.classcode '班別',
a.subj '科目',
a.subjcomp '科目分卷',
a.fullscore '科目滿分',
a.highest '最高分',
a.lowest '最低分',
a.avge '平均分',
a.pass '合格率'
from(
select
a.classcode 'classcode',
e.ch_des 'subj',
d.ch_des 'subjcomp',
d1.fullscore,
max(c.sysscore) 'highest',
min(c.sysscore) 'lowest',
str(round(avg(c.sysscore), 2), 10, 2) 'avge',
str(round(sum((case when c.sysscore >= d1.fullscore*0.5 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' 'pass'
from vw_stu_lateststudent a
join tb_asr_time b
on b.timeseq=?
join tb_asr_subjcomassessdata c
on a.suid=c.suid and a.stuid=c.stuid and a.schlvl=c.schlevel and a.schsess=c.schsession and
a.schyear=c.schyear and a.classlvl=c.classlevel and c.timeseq=b.timeseq
left outer join wsadmin.tb_asr_clslvlsubjsetting d1 on c.suid=d1.suid and c.schlevel=d1.schlevel and c.schsession=d1.schsession
and c.classlevel=d1.classlevel and c.schyear=d1.schyear and d1.timeseq=c.timeseq and d1.subjcode=c.subjcode and d1.moi=c.moi
left outer join tb_hse_sbjcmp d
on a.suid=d.suid and c.subjcode=d.sbj_code and c.subjcompcode=d.code_id
left outer join tb_hse_common e
on a.suid=e.suid and c.subjcode=e.code_id and e.tb_id='SBJ'
where a.schyear=? and a.classlvl=?
group by a.classcode, c.subjcode, e.ch_des, d.ch_des, d1.fullscore
union all
select
a.classcode 'classcode',
e.ch_des 'subj',
null 'subjcomp',
d1.fullscore,
max(c.sysscore) 'highest',
min(c.sysscore) 'lowest',
str(round(avg(c.sysscore), 2), 10, 2) 'avge',
str(round(sum((case when c.sysscore >= d1.fullscore*0.5 then 1.0 else 0.0 end)) / count(c.stuid) * 100, 2), 10, 2) || '%' 'pass'
from vw_stu_lateststudent a
join tb_asr_time b
on b.timeseq=?
join tb_asr_subjassessdata c
on a.suid=c.suid and a.stuid=c.stuid and a.schlvl=c.schlevel and a.schsess=c.schsession and
a.schyear=c.schyear and a.classlvl=c.classlevel and c.timeseq=b.timeseq
left outer join wsadmin.tb_asr_clslvlsubjsetting d1 on c.suid=d1.suid and c.schlevel=d1.schlevel and c.schsession=d1.schsession
and c.classlevel=d1.classlevel and c.schyear=d1.schyear and d1.timeseq=c.timeseq and d1.subjcode=c.subjcode and d1.moi=c.moi
left outer join tb_hse_common e
on a.suid=e.suid and c.subjcode=e.code_id and e.tb_id='SBJ'
where a.schyear=? and a.classlvl=?
group by a.classcode, c.subjcode, e.ch_des, d1.fullscore) a
order by 1,2,3

#4 edb-catherinewschan, 2021-07-12