SQL 抽取P1-6平均分和及格百分比分析(依科目及科目分卷)

1. 偉年 Expand Collapse

文章:
17
讚:
0
你好, 請問可否幫忙修改以下P1-6平均分和及格百分比分析(依科目)的SQL
因為本人想再抽取多一些資料 ---- 中文科目分卷(01中文讀本、02中文默書、03中文作文) 和 英文科目分卷(01英文讀本、02英文默書、04英文聆聽)

select
(case when x.schlvl=2 then 'P' else 'S' end) || x.classcode 'Class',
s.ch_des,
sum(case when y.syspercscore<=9.9 and y.syspercscore>=0.0 then 1 else 0 end) '0-9',

sum(case when y.syspercscore<=19.9 and y.syspercscore>=10.0 then 1 else 0 end) '10-19',

sum(case when y.syspercscore<=29.9 and y.syspercscore>=20.0 then 1 else 0 end) '20-29',

sum(case when y.syspercscore<=39.9 and y.syspercscore>=30.0 then 1 else 0 end) '30-39',

sum(case when y.syspercscore<=49.9 and y.syspercscore>=40.0 then 1 else 0 end) '40-49',

sum(case when y.syspercscore<=59.9 and y.syspercscore>=50.0 then 1 else 0 end) '50-59',

sum(case when y.syspercscore<=69.9 and y.syspercscore>=60.0 then 1 else 0 end) '60-69',

sum(case when y.syspercscore<=79.9 and y.syspercscore>=70.0 then 1 else 0 end) '70-79',

sum(case when y.syspercscore<=89.9 and y.syspercscore>=80.0 then 1 else 0 end) '80-89',

sum(case when y.syspercscore<=99.9 and y.syspercscore>=90.0 then 1 else 0 end) '90-99',

sum(case when y.syspercscore=100.0 then 1 else 0 end) '100',

str(round((case when count(y.sysscore)=0 then 0 else ((sum(case when z2.absolutegrademark>=z1.absolutegrademark then 1 else 0 end)*1.0 / count(y.sysscore)) * 100) end), 2), 10, 2) 'passed_percent',

str(round((case when count(y.sysscore)=0 then 0 else avg(y.sysscore) end), 2), 10, 2) 'average',

str(round((case when count(y.sysscore)=0 then 0 else ((sqrt(sum(y.sysscore*y.sysscore)*1.0/count(y.sysscore)-avg(y.sysscore)*avg(y.sysscore)))) end), 2), 10, 2) 'StandardDev',

str(round((case when count(y.syspercscore)=0 then 0 else avg(y.syspercscore) end), 2), 10, 2) 'average_percent'
from vw_stu_lateststudent x
left outer join tb_hse_common s
on x.suid=s.suid and s.code_id in ('080', '165', '280', '205', '185') and s.tb_id='SBJ'
left outer join tb_asr_subjassessdata y
on x.suid=y.suid and x.stuid=y.stuid and x.schyear=y.schyear and x.schlvl=y.schlevel and x.schsess=y.schsession and x.classlvl=y.classlevel and y.timeseq=? and y.subjcode=s.code_id

where x.schyear=?
group by s.ch_des, x.classcode, x.schlvl

union all

select
x.classlvl || 'Z',
s.ch_des,
sum(case when y.syspercscore<=9.9 and y.syspercscore>=0.0 then 1 else 0 end) '0-9',

sum(case when y.syspercscore<=19.9 and
y.syspercscore>=10.0 then 1 else 0 end) '10-19',

sum(case when y.syspercscore<=29.9 and
y.syspercscore>=20.0 then 1 else 0 end) '20-29',

sum(case when y.syspercscore<=39.9 and
y.syspercscore>=30.0 then 1 else 0 end) '30-39',

sum(case when y.syspercscore<=49.9 and
y.syspercscore>=40.0 then 1 else 0 end) '40-49',

sum(case when y.syspercscore<=59.9 and
y.syspercscore>=50.0 then 1 else 0 end) '50-59',

sum(case when y.syspercscore<=69.9 and
y.syspercscore>=60.0 then 1 else 0 end) '60-69',

sum(case when y.syspercscore<=79.9 and
y.syspercscore>=70.0 then 1 else 0 end) '70-79',

sum(case when y.syspercscore<=89.9 and
y.syspercscore>=80.0 then 1 else 0 end) '80-89',

sum(case when y.syspercscore<=99.9 and
y.syspercscore>=90.0 then 1 else 0 end) '90-99',

sum(case when y.syspercscore=100.0 then 1 else 0 end) '100',

str(round((case when count(y.sysscore)=0 then 0 else ((sum(case when z2.absolutegrademark>=z1.absolutegrademark then 1 else 0 end)*1.0 / count(y.sysscore)) * 100) end), 2), 10, 2) 'passed_percent',
str(round((case when count(y.sysscore)=0 then 0 else avg(y.sysscore) end), 2), 10, 2) 'average',
str(round((case when count(y.sysscore)=0 then 0 else ((sqrt(sum(y.sysscore*y.sysscore)*1.0/count(y.sysscore)-avg(y.sysscore)*avg(y.sysscore)))) end), 2), 10, 2) 'StandardDev',
str(round((case when count(y.syspercscore)=0 then 0 else avg(y.syspercscore) end), 2), 10, 2) 'average_percent'
from vw_stu_lateststudent x
left outer join tb_hse_common s
on x.suid=s.suid and s.code_id in ('080', '165', '280', '205', '185') and s.tb_id='SBJ'
left outer join tb_asr_subjassessdata y
on x.suid=y.suid and x.stuid=y.stuid and x.schyear=y.schyear and x.schlvl=y.schlevel and x.schsess=y.schsession and x.classlvl=y.classlevel and y.timeseq=? and y.subjcode=s.code_id

where x.schyear=?
group by s.ch_des, x.classlvl

order by 2,1

2. 偉年 Expand Collapse

文章:
17
讚:
0
各位好,
現時以上用的SQL只能抽取中國語文、英國語文、數學、常識、倫理的分數作統計資料，、
請問可否修改以上的SQL 抽取中文科目分卷(01中文讀本、02中文默書、03中文作文) 和 英文科目分卷(01英文讀本、02英文默書、04英文聆聽)的分數統計?

請各位幫忙。
謝謝

3. edb-catherinewschan Expand Collapse

文章:
184
讚:
0
這個比較複雜，要些時間研究一下。

#3 edb-catherinewschan, 2021-04-23
4. edb-catherinewschan Expand Collapse

文章:
184
讚:
0
你好,

改完之後，可能是結構過於複雜或某些功能不支援，發現未能通過在WEBSAMS內執行，但在其他地方是可以的。

select
(case when x.schlvl=2 then 'P' else 'S' end) || x.classcode 'Class',
s.ch_des,
d.ch_des,
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=9.9 and y.syspercscore>=0.0 then 1 else 0 end) else (case when c.syspercscore<=9.9 and c.syspercscore>=0.0 then 1 else 0 end) end ) '0-9',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=19.9 and y.syspercscore>=10.0 then 1 else 0 end) else (case when c.syspercscore<=19.9 and c.syspercscore>=10.0 then 1 else 0 end) end ) '10-19',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=29.9 and y.syspercscore>=20.0 then 1 else 0 end) else (case when c.syspercscore<=29.9 and c.syspercscore>=20.0 then 1 else 0 end) end ) '20-29',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=39.9 and y.syspercscore>=30.0 then 1 else 0 end) else (case when c.syspercscore<=39.9 and c.syspercscore>=30.0 then 1 else 0 end) end ) '30-39',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=49.9 and y.syspercscore>=40.0 then 1 else 0 end) else (case when c.syspercscore<=49.9 and c.syspercscore>=40.0 then 1 else 0 end) end ) '40-49',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=59.9 and y.syspercscore>=50.0 then 1 else 0 end) else (case when c.syspercscore<=59.9 and c.syspercscore>=50.0 then 1 else 0 end) end ) '50-59',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=69.9 and y.syspercscore>=60.0 then 1 else 0 end) else (case when c.syspercscore<=69.9 and c.syspercscore>=60.0 then 1 else 0 end) end ) '60-69',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=79.9 and y.syspercscore>=70.0 then 1 else 0 end) else (case when c.syspercscore<=79.9 and c.syspercscore>=70.0 then 1 else 0 end) end ) '70-79',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=89.9 and y.syspercscore>=80.0 then 1 else 0 end) else (case when c.syspercscore<=89.9 and c.syspercscore>=80.0 then 1 else 0 end) end ) '80-89',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=99.9 and y.syspercscore>=90.0 then 1 else 0 end) else (case when c.syspercscore<=99.9 and c.syspercscore>=90.0 then 1 else 0 end) end ) '90-99',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore=100 then 1 else 0 end) else (case when c.syspercscore=100 then 1 else 0 end) end ) '100',

IF c.SUBJCOMPCODE is null THEN
(str(round((case when count(y.sysscore)=0 then 0 else ((sum(case when z2.absolutegrademark>=z1.absolutegrademark then 1 else 0 end)*1.0 / count(y.sysscore)) * 100) end), 2), 10, 2))
ELSE
(str(round((case when count(c.sysscore)=0 then 0 else ((sum(case when z2.absolutegrademark>=z1.absolutegrademark then 1 else 0 end)*1.0 / count(c.sysscore)) * 100) end), 2), 10, 2))
END IF 'passed_percent',
IF c.SUBJCOMPCODE is null THEN
(str(round((case when count(y.sysscore)=0 then 0 else avg(y.sysscore) end), 2), 10, 2))
ELSE
(str(round((case when count(c.sysscore)=0 then 0 else avg(c.sysscore) end), 2), 10, 2))
END IF 'average',
IF c.SUBJCOMPCODE is null THEN
(str(round((case when count(y.sysscore)=0 then 0 else ((sqrt(sum(y.sysscore*y.sysscore)*1.0/count(y.sysscore)-avg(y.sysscore)*avg(y.sysscore)))) end), 2), 10, 2))
ELSE
(str(round((case when count(c.sysscore)=0 then 0 else ((sqrt(sum(c.sysscore*c.sysscore)*1.0/count(c.sysscore)-avg(c.sysscore)*avg(c.sysscore)))) end), 2), 10, 2))
END IF 'StandardDev',
IF c.SUBJCOMPCODE is null THEN
(str(round((case when count(y.syspercscore)=0 then 0 else avg(y.syspercscore) end), 2), 10, 2))
ELSE
(str(round((case when count(c.syspercscore)=0 then 0 else avg(c.syspercscore) end), 2), 10, 2))
END IF 'average_percent'

on x.suid=y.suid and x.stuid=y.stuid and x.schyear=y.schyear and x.schlvl=y.schlevel and x.schsess=y.schsession and x.classlvl=y.classlevel and y.timeseq=?
and y.subjcode in ('080', '165', '280', '205', '185')

left outer join wsadmin.tb_hse_common as s
on y.suid=s.suid and y.SUBJCODE = s.CODE_ID and s.tb_id='SBJ'

on c.suid=y.suid and c.stuid=y.stuid and c.schyear=y.schyear and c.schlevel=y.schlevel and c.schsession=y.schsession and c.classlevel=y.classlevel
and c.timeseq=y.timeseq and c.subjcode=y.subjcode

left outer join wsadmin.TB_HSE_SBJCMP d on c.SUID = d.SUID and c.SUBJCODE = d.SBJ_CODE and c.SUBJCOMPCODE = d.CODE_ID

where x.schyear=? and y.subjcode is not null
group by y.subjcode, c.SUBJCOMPCODE, s.ch_des, d.ch_des, x.classcode, x.schlvl

UNION ALL

select
x.classlvl || 'Z',
s.ch_des,
d.ch_des,
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=9.9 and y.syspercscore>=0.0 then 1 else 0 end) else (case when c.syspercscore<=9.9 and c.syspercscore>=0.0 then 1 else 0 end) end ) '0-9',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=19.9 and y.syspercscore>=10.0 then 1 else 0 end) else (case when c.syspercscore<=19.9 and c.syspercscore>=10.0 then 1 else 0 end) end ) '10-19',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=29.9 and y.syspercscore>=20.0 then 1 else 0 end) else (case when c.syspercscore<=29.9 and c.syspercscore>=20.0 then 1 else 0 end) end ) '20-29',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=39.9 and y.syspercscore>=30.0 then 1 else 0 end) else (case when c.syspercscore<=39.9 and c.syspercscore>=30.0 then 1 else 0 end) end ) '30-39',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=49.9 and y.syspercscore>=40.0 then 1 else 0 end) else (case when c.syspercscore<=49.9 and c.syspercscore>=40.0 then 1 else 0 end) end ) '40-49',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=59.9 and y.syspercscore>=50.0 then 1 else 0 end) else (case when c.syspercscore<=59.9 and c.syspercscore>=50.0 then 1 else 0 end) end ) '50-59',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=69.9 and y.syspercscore>=60.0 then 1 else 0 end) else (case when c.syspercscore<=69.9 and c.syspercscore>=60.0 then 1 else 0 end) end ) '60-69',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=79.9 and y.syspercscore>=70.0 then 1 else 0 end) else (case when c.syspercscore<=79.9 and c.syspercscore>=70.0 then 1 else 0 end) end ) '70-79',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=89.9 and y.syspercscore>=80.0 then 1 else 0 end) else (case when c.syspercscore<=89.9 and c.syspercscore>=80.0 then 1 else 0 end) end ) '80-89',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore<=99.9 and y.syspercscore>=90.0 then 1 else 0 end) else (case when c.syspercscore<=99.9 and c.syspercscore>=90.0 then 1 else 0 end) end ) '90-99',
sum(case when c.SUBJCOMPCODE is null then (case when y.syspercscore=100 then 1 else 0 end) else (case when c.syspercscore=100 then 1 else 0 end) end ) '100',

IF c.SUBJCOMPCODE is null THEN
(str(round((case when count(y.sysscore)=0 then 0 else ((sum(case when z2.absolutegrademark>=z1.absolutegrademark then 1 else 0 end)*1.0 / count(y.sysscore)) * 100) end), 2), 10, 2))
ELSE
(str(round((case when count(c.sysscore)=0 then 0 else ((sum(case when z2.absolutegrademark>=z1.absolutegrademark then 1 else 0 end)*1.0 / count(c.sysscore)) * 100) end), 2), 10, 2))
END IF 'passed_percent',
IF c.SUBJCOMPCODE is null THEN
(str(round((case when count(y.sysscore)=0 then 0 else avg(y.sysscore) end), 2), 10, 2))
ELSE
(str(round((case when count(c.sysscore)=0 then 0 else avg(c.sysscore) end), 2), 10, 2))
END IF 'average',
IF c.SUBJCOMPCODE is null THEN
(str(round((case when count(y.sysscore)=0 then 0 else ((sqrt(sum(y.sysscore*y.sysscore)*1.0/count(y.sysscore)-avg(y.sysscore)*avg(y.sysscore)))) end), 2), 10, 2))
ELSE
(str(round((case when count(c.sysscore)=0 then 0 else ((sqrt(sum(c.sysscore*c.sysscore)*1.0/count(c.sysscore)-avg(c.sysscore)*avg(c.sysscore)))) end), 2), 10, 2))
END IF 'StandardDev',
IF c.SUBJCOMPCODE is null THEN
(str(round((case when count(y.syspercscore)=0 then 0 else avg(y.syspercscore) end), 2), 10, 2))
ELSE
(str(round((case when count(c.syspercscore)=0 then 0 else avg(c.syspercscore) end), 2), 10, 2))
END IF 'average_percent'

on x.suid=y.suid and x.stuid=y.stuid and x.schyear=y.schyear and x.schlvl=y.schlevel and x.schsess=y.schsession and x.classlvl=y.classlevel and y.timeseq=?
and y.subjcode in ('080', '165', '280', '205', '185')

left outer join wsadmin.tb_hse_common as s
on y.suid=s.suid and y.SUBJCODE = s.CODE_ID and s.tb_id='SBJ'

on c.suid=y.suid and c.stuid=y.stuid and c.schyear=y.schyear and c.schlevel=y.schlevel and c.schsession=y.schsession and c.classlevel=y.classlevel
and c.timeseq=y.timeseq and c.subjcode=y.subjcode

left outer join wsadmin.TB_HSE_SBJCMP d on c.SUID = d.SUID and c.SUBJCODE = d.SBJ_CODE and c.SUBJCOMPCODE = d.CODE_ID

where x.schyear=? and y.subjcode is not null
group by y.subjcode, c.SUBJCOMPCODE, s.ch_des, d.ch_des, x.classcode, x.classlvl
order by 1,2,3

#4 edb-catherinewschan, 2021-04-26
5. 偉年 Expand Collapse

文章:
17
讚:
0
謝謝你的幫忙,

但想請問是在甚麼地方可執行?

6. edb-catherinewschan Expand Collapse

文章:
184
讚:
0
你們可以用crystal report, 加入command貼上SQL去取資料