select aaa.*,bbb.required,bbb.elective from
(select c.student_id,c.chinese_name,c.user_class, sum(decode(b.coursetype,1,a.credit,0)) get_required_total,sum(decode(b.coursetype,0,a.credit,0)) get_elective_total, sum(decode(b.coursetype,1,a.coursecredit,0)) reg_required_total,sum(decode(b.coursetype,0,a.coursecredit,0)) reg_elective_total
from sm_course_student a,sm_courses b,sm_students c
where a.course_id=b.courseid and a.student_id=c.student_id and c.user_class like '10SH%' group by c.student_id,c.chinese_name,c.user_class
(select c.student_id,c.chinese_name,c.user_class, sum(decode(b.coursetype,1,a.credit,0)) get_required_total,sum(decode(b.coursetype,0,a.credit,0)) get_elective_total, sum(decode(b.coursetype,1,a.coursecredit,0)) reg_required_total,sum(decode(b.coursetype,0,a.coursecredit,0)) reg_elective_total
from sm_course_student a,sm_courses b,sm_students c
where a.course_id=b.courseid and a.student_id=c.student_id and c.user_class like '10SH%' group by c.student_id,c.chinese_name,c.user_class
)aaa,
(select studentid,required,elective from sm_school_roll where classname like '10SH%'
)bbb
where aaa.student_id = bbb.studentid