SQL抽取合格科目數目

回覆 20# tr-gilbertng


請加入 distinct 以除去重覆的資料，謝謝！

select
b.classcode 'class', b.classno, b.enname, b.chname,
b.sex, p.failed
from tb_stu_student b

left outer join
(select d.suid, d.stuid, d.schyear, d.timeseq,
list(distinct sn.CH_DES) as 'failed'
from tb_asr_studassessdata d
left outer join tb_asr_subjassessdata a
on d.suid=a.suid and d.stuid=a.stuid and
d.schyear=a.schyear and d.timeseq=a.timeseq
left outer join tb_hse_common sn
on sn.suid=a.suid and sn.TB_ID='SBJ' and
sn.CODE_ID=a.SUBJCODE
left outer join tb_asr_clslvlsubjsetting s
on d.suid=s.suid and d.schyear=s.schyear and
d.schlevel=s.schlevel and d.schsession=s.schsession and
d.classlevel=s.classlevel and d.timeseq=s.timeseq and
a.subjcode=s.subjcode
left outer join tb_asr_gradeconversioncomp g
on d.suid=g.suid and d.schyear=g.schyear and
a.sysgradeconvseq=g.gradeconversionseq and
a.sysgradeconvcompcode=g.gradeconversioncompcode
left outer join tb_asr_gradeconversioncomp gp
on d.suid=gp.suid and d.schyear=gp.schyear and
a.sysgradeconvseq=gp.gradeconversionseq and
gp.passinggradeind='Y'
where s.bygradeind='N' and
g.absolutegrademark >= gp.absolutegrademark and
d.schyear=? and
d.timeseq=?
group by d.suid, d.stuid, d.schyear, d.timeseq
) p
on b.suid=p.suid and b.stuid=p.stuid

left outer join tb_asr_studassessdata d1
on b.suid=d1.suid and b.stuid=d1.stuid and
p.schyear=d1.schyear and p.timeseq=d1.timeseq

where
b.classlvl=? and
b.classcode is not null and b.classcode<>''
order by
b.SUID, b.SCHLVL, b.SCHSESS, b.CLASSLVL,
b.classcode, b.classno

 

謝謝你的幫忙, 如果要將取得這些合格科目的數值, 是否要使用Count? 語法是.....? 謝謝!

 

回覆 22# tr-gilbertng

請問是指那些(只有不合格）科目或全部及格科目的數值，還有是以每班或每級作統計，謝謝！

 

我想要的是在以上的表增加多一欄, 該欄是顯示該學生的合格科目數目:

班別 學號 姓名 合格科目 合格科目數目
4A 1 xxx M 化學,中國語文,經濟,英國語文,數學（必修部分）,通識教育 (6)
4A 2 xxx M 化學,中國語文,經濟,英國語文,數學（必修部分）(5)

謝謝

 

回覆 24# tr-gilbertng

請老師參考以下的 SQL 句子：

select
b.classcode '班別',
b.classno '學號',
b.chname '中文姓名',
b.enname '英文姓名',
p.passed '合格科目',
ifnull(p.passing,0,p.passing) as '合格科目數目'
from tb_stu_student b

left outer join
(select d.suid, d.stuid, count(distinct sn.CH_DES) as 'passing', list(distinct sn.CH_DES) as 'passed'
from tb_asr_studassessdata d
left outer join tb_asr_subjassessdata a
on d.suid=a.suid and d.stuid=a.stuid and d.timeseq=a.timeseq and d.schyear=a.schyear
left outer join tb_asr_gradeconversioncomp g on d.suid=g.suid and d.schyear=g.schyear and
a.sysgradeconvseq=g.gradeconversionseq and a.sysgradeconvcompcode=g.gradeconversioncompcode
left outer join tb_asr_gradeconversioncomp gp on d.suid=gp.suid and d.schyear=gp.schyear and
a.sysgradeconvseq=gp.gradeconversionseq and gp.passinggradeind='Y'
left outer join tb_hse_common sn
on sn.suid=a.suid and sn.TB_ID='SBJ' and sn.CODE_ID=a.SUBJCODE
left outer join tb_asr_clslvlsubjsetting s
on d.suid=s.suid and d.schyear=s.schyear and d.schlevel=s.schlevel and d.schsession=s.schsession and
d.classlevel=s.classlevel and d.timeseq=s.timeseq and
a.subjcode=s.subjcode
where g.absolutegrademark >= gp.absolutegrademark and d.schyear=? and d.timeseq=?
group by d.suid, d.stuid) p
on b.suid=p.suid and b.stuid=p.stuid
where b.classlvl=? and b.classcode<>''
order by b.classcode, b.classno