select eid,name,deparrment,course,grade
   from (
  select a.*,b.course,b.grade,row_number() over(partition by b.course order by grade desc) rn
       from a,trainging  b
       where a.eid=b.eid)
       where rn=1

------解决方案--------------------

2
SQL code
 select course,
        sum( case
             when grade >=85 and grade<=100 then 1
             else 0
             end
            )   "【100-85】",
        sum( case
             when grade >=70 and grade<85 then 1
             else 0
             end
            )   "【84-70】",
        sum( case
             when grade >=60 and grade<70 then 1
             else 0
             end
            )   "【69-60】",
        sum( case
             when grade <60 then 1
             else 0
             end
            )   "【<60】"
        from trainging 
        group by course

------解决方案--------------------

SQL code

--不用row_number() over()的方法
select a.eid,a.name,a.department,d.course,d.grade from student a,
(select b.eid,b.course,b.grade from trainging b,
(select course,max(grade) max_grade from trainging group by course ) c 
where b.course=c.course and b.grade=c.grade
)d
where a.eid=d.eid;

--冒用一下2楼的，呵呵：
select course,
        sum( case
             when grade >=85 and grade<=100 then 1
             else 0
             end
            )   "【100-85】",
        sum( case
             when grade >=70 and grade<85 then 1
             else 0
             end
            )   "【84-70】",
        sum( case
             when grade >=60 and grade<70 then 1
             else 0
             end
            )   "【69-60】",
        sum( case
             when grade <60 then 1
             else 0
             end
            )   "【<60】"
        from trainging 
        group by course