其他 DSE成績:請問如何顯示分卷名稱?

多謝 sams cdr 提供抽取 DSE 成績的 sql。

但分卷只顯示代號(01, 02...), 請問如何顯示分卷名稱(如:作文、聆聽)?

select
a.EXAMCODE,
a.examyear,
a.classcode,
a.classno,
a1.regno,
a1.ENNAME,
a1.CHNAME,
a1.sex,
b.subjcode 'Subject Code',
b1.DSESUBJGROUPNAME_ENG||' ('||b1.DSESUBJGROUPNAME_CHI||')' 'Subject Name',
b.subjgrdlvl 'Subject Grade',
c.subjcompcode 'Subject Comp Code',
(case when c1.dsesubjcompname_eng is null then '' else c1.dsesubjcompname_eng||' ('||c1.dsesubjcompname_chi||')' end) 'Subject Comp Name',
c.subjcompgrdlvl 'Subject Comp Grade'
from tb_hke_dsecandinfo a
left outer join tb_stu_student a1
on a.suid=a1.suid and a.stuid=a1.stuid
left outer join tb_hke_dsesubjresult b
on a.suid=b.suid and a.schcode=b.schcode and a.candno=b.candno and a.examyear=b.examyear
left outer join
(select distinct SUID, EXAMYEAR, EXAMCODE, DSESUBJGROUPCODE, DSESUBJGROUPNAME_ENG, DSESUBJGROUPNAME_CHI
from tb_hke_regexamsubjgrp group by SUID, EXAMYEAR, EXAMCODE, DSESUBJGROUPCODE, DSESUBJGROUPNAME_ENG, DSESUBJGROUPNAME_CHI) b1
on b.suid=b1.suid and b.examyear=b1.examyear and b1.examcode='DSE' and b.subjcode=b1.DSESUBJGROUPCODE
left outer join tb_hke_dsesubjcompresult c
on b.suid=c.suid and b.schcode=c.schcode and b.candno=c.candno and b.examyear=c.examyear and b.subjcode=c.subjcode
left outer join tb_hke_regexamsubjcomp c1
on c.suid=c1.suid and c.examyear=c1.examyear and c1.examcode='DSE' and c1.dsesubjcomptype='RESULT' and c.subjcode=c1.dsesubjgroupcode and c.subjcompcode=c1.dsesubjcompcode
where a.examyear=?
order by 1,2,3,4,9,12

 

老師請參考以下的 SQL 語句。

select
a.EXAMCODE,
a.examyear,
a.classcode,
a.classno,
a1.regno,
a1.ENNAME,
a1.CHNAME,
a1.sex,
b.subjcode 'Subject Code',
b1.DSESUBJGROUPNAME_ENG||' ('||b1.DSESUBJGROUPNAME_CHI||')' 'Subject Name',
b.subjgrdlvl 'Subject Grade',
c.subjcompcode 'Subject Comp Code',
c1.dsesubjcompname_eng||' '||c1.dsesubjcompname_chi 'Subject Comp Name',
c.subjcompgrdlvl 'Subject Comp Grade'
from tb_hke_dsecandinfo a
left outer join tb_stu_student a1
on a.suid=a1.suid and a.stuid=a1.stuid
left outer join tb_hke_dsesubjresult b
on a.suid=b.suid and a.schcode=b.schcode and a.candno=b.candno and a.examyear=b.examyear
left outer join
(select distinct SUID, EXAMYEAR, EXAMCODE, DSESUBJGROUPCODE, DSESUBJGROUPNAME_ENG, DSESUBJGROUPNAME_CHI
from tb_hke_regexamsubjgrp group by SUID, EXAMYEAR, EXAMCODE, DSESUBJGROUPCODE, DSESUBJGROUPNAME_ENG, DSESUBJGROUPNAME_CHI) b1
on b.suid=b1.suid and b.examyear=b1.examyear and b1.examcode='DSE' and b.subjcode=b1.DSESUBJGROUPCODE
left outer join tb_hke_dsesubjcompresult c
on b.suid=c.suid and b.schcode=c.schcode and b.candno=c.candno and b.examyear=c.examyear and b.subjcode=c.subjcode
left outer join tb_hke_regexamsubjcomp c1
on c.suid=c1.suid and c.examyear=c1.examyear and c1.examcode='DSE' and c1.dsesubjcomptype='RESULT' and c.subjcode=c1.dsesubjgroupcode and c.subjcompcode=c1.dsesubjcompcode
where a.examyear=?
order by 1,2,3,4,9,12

 

thank you!