绕人的sql 语句!

select sca.courseid ,count(sca.stuid) as seq, c.name,max((sca.commonscore+sca.endscore)/2) as maxscore,c.test as test ,scb.term as term , scb.commonscore as commonscore ,scb.endscore as endscore , (scb.commonscore+scb.endscore)/2 as lastscore from studentcourse sca ,studentcourse scb ,course c where sca.courseid=scb.courseid and scb.stuid='2003131214' and (sca.commonscore+sca.endscore)/2 >= (scb.commonscore+scb.endscore)/2 and scb.istest ='1' and c.id=sca.courseid group by sca.courseid,c.name,c.test,scb.commonscore,scb.endscore,(scb.commonscore+scb.endscore)/2 ,scb.term order by scb.term 统计学生成绩名次,最高分