)Рselect sdept,ssex,max(sum_grade) as max_sum Рfrom ( select sdept,ssex,sname,sum(grade) as sum_grade Р from all_dataР group by sdept,ssex,sname) as grade_sum(sdept,ssex,sname,sum_grade)Рgroup by sdept,ssexРorder by sdept,ssexР24、分系统计总分最低分(3)Рselect sdept,min(sum_grade) as min_sum Рfrom ( select sdept,sname,sum(grade) as sum_grade Р from all_dataР group by sdept,sname) as grade_sum(sdept,sname,sum_grade)Рgroup by sdeptРorder by sdeptР25、统计各门课程各分数段人数,每10分为一个分数段,比如成绩65分划为60这个分数段。(提示用计算列ROUND(grade-5,-1计算每个成绩的分数段)(42)Рame,grade_rank,count(*) as num from (Р ame,round(grade-5,-1) as grade_rank Р from all_data ) as ame,grade_rank)Рgroup ame,grade_rankРorder ame,grade_rankР26、统计每个学生总学分(注:成绩及格才计学分)(254)Рselect sno,redit) as sum_cedit Рfrom sc,courseРwhere o=o and grade >0Рgroup by snoРorder by sno
全文预览