SQL 抽取 兩個學期T1A1 同T2A1，每一級頭十名最進步學生成績同時要操行要B以上 的SQL

想將這個sql 額外再加一個條件: 全年平均分大於或等於50
select
a.CLASSCODE,
a.CLASSNO,
a.CHNAME,
c.SYSPERCSCORE 'T1A1',
c.overcondgradeconversioncompcode 'T1A1 Conduct',
d.SYSPERCSCORE 'T2A1',
d.overcondgradeconversioncompcode 'T2A1 Conduct',
d.SYSPERCSCORE - c.SYSPERCSCORE 'Diff'
from VW_STU_LATESTSTUDENT a
join TB_ASR_STUDASSESSDATA c
on a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR and c.TIMESEQ = 1101 and c.overcondgradeconversioncompcode in ('A','A+', 'A-', 'B+')
left outer join TB_ASR_STUDASSESSDATA d
on a.SUID = d.SUID and a.STUID = d.STUID and a.SCHYEAR = d.SCHYEAR and d.TIMESEQ = 1201 and d.overcondgradeconversioncompcode in ('A','A+', 'A-', 'B+')
where a.classlvl=? and a.schyear=?
order by Diff desc
應如何處理?謝謝

 

老師，請參考以下，

select
a.CLASSCODE,
a.CLASSNO,
a.CHNAME,
c.SYSPERCSCORE 'T1A1',
c.overcondgradeconversioncompcode 'T1A1 Conduct',
d.SYSPERCSCORE 'T2A1',
d.overcondgradeconversioncompcode 'T2A1 Conduct',
d.SYSPERCSCORE - c.SYSPERCSCORE 'Diff',
e.syspercscore 'Annual_Average'
from VW_STU_LATESTSTUDENT a
join TB_ASR_STUDASSESSDATA c
on a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR and c.TIMESEQ = 1101 and c.overcondgradeconversioncompcode in ('A','A+', 'A-', 'B+')
left outer join TB_ASR_STUDASSESSDATA d
on a.SUID = d.SUID and a.STUID = d.STUID and a.SCHYEAR = d.SCHYEAR and d.TIMESEQ = 1201 and d.overcondgradeconversioncompcode in ('A','A+', 'A-', 'B+')
left outer join TB_ASR_STUDASSESSDATA e
on a.SUID = e.SUID and a.STUID = e.STUID and a.SCHYEAR = e.SCHYEAR and e.TIMESEQ = 1000
where e.syspercscore >= 50 and a.classlvl=? and a.schyear=?
order by Diff desc