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

本文由 偉年 在 2021-04-19 發表於 "WebSAMS 討論區" 討論區

  1. 55061472

    偉年
    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
    left outer join tb_asr_gradeconversioncomp z1

    on x.suid=z1.suid and x.schyear=z1.schyear and y.sysgradeconvseq=z1.gradeconversionseq and z1.passinggradeind='Y'
    left outer join tb_asr_gradeconversioncomp z2

    on x.suid=z2.suid and x.schyear=z2.schyear and y.sysgradeconvseq=z2.gradeconversionseq and y.sysgradeconvcompcode=z2.gradeconversioncompcode
    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

    left outer join tb_asr_gradeconversioncomp z1
    on x.suid=z1.suid and x.schyear=z1.schyear and y.sysgradeconvseq=z1.gradeconversionseq and z1.passinggradeind='Y'

    left outer join tb_asr_gradeconversioncomp z2
    on x.suid=z2.suid and x.schyear=z2.schyear and y.sysgradeconvseq=z2.gradeconversionseq and y.sysgradeconvcompcode=z2.gradeconversioncompcode
    where x.schyear=?
    group by s.ch_des, x.classlvl

    order by 2,1
     
  2. 55061472

    偉年
    Expand Collapse

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

    請各位幫忙。
    謝謝
     
  3. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    184
    讚:
    0
    這個比較複雜,要些時間研究一下。
     
    #3 edb-catherinewschan, 2021-04-23
  4. 58521906

    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'

    from wsadmin.vw_stu_lateststudent x

    left outer join wsadmin.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 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'

    left outer join wsadmin.tb_asr_subjcomassessdata c
    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

    left outer join wsadmin.tb_asr_gradeconversioncomp z1
    on x.suid=z1.suid and x.schyear=z1.schyear and y.sysgradeconvseq=z1.gradeconversionseq and z1.passinggradeind='Y'

    left outer join wsadmin.tb_asr_gradeconversioncomp z2
    on x.suid=z2.suid and x.schyear=z2.schyear and y.sysgradeconvseq=z2.gradeconversionseq and y.sysgradeconvcompcode=z2.gradeconversioncompcode

    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'

    from wsadmin.vw_stu_lateststudent x

    left outer join wsadmin.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 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'

    left outer join wsadmin.tb_asr_subjcomassessdata c
    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

    left outer join wsadmin.tb_asr_gradeconversioncomp z1
    on x.suid=z1.suid and x.schyear=z1.schyear and y.sysgradeconvseq=z1.gradeconversionseq and z1.passinggradeind='Y'

    left outer join wsadmin.tb_asr_gradeconversioncomp z2
    on x.suid=z2.suid and x.schyear=z2.schyear and y.sysgradeconvseq=z2.gradeconversionseq and y.sysgradeconvcompcode=z2.gradeconversioncompcode

    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. 55061472

    偉年
    Expand Collapse

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

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

    edb-catherinewschan
    Expand Collapse

    文章:
    184
    讚:
    0
    你們可以用crystal report, 加入command貼上SQL去取資料
    如果有上過Crystal Report ADV 的課程,都有教你們怎去用SQL取資料顯示在CRYSTAL REPORT 上。
    類似附件的做法
     

    附件文件:

    #6 edb-catherinewschan, 2021-04-26