数据库有关问题

数据库问题
eid |name|department|job email 
1001 |李明|LUCK |EG 
1003 |李杰|SBB |TM 
10045|李燕|MTD |NT 
10044|李明|LUKE |EG 
10023|小刚|SBB |EG 
20001|张清|MTD |ENT 

表2.trainging 
courseID| EID |course |grade 
1 |11045 |T-SQL |60 
3 |20460 |java |23 
2 |10001 |Oracle|90 
1 |20078 |java |78 
3 |30001 |Oracle|60 
列出所有各科成绩最高的员工信息，要求显示eid,name,deparrment,course,grade,用一条SQL语句

统计各门课程成绩各分数段人数 【100-85】【84-70】【69-60】【<60】

[解决办法]
1 

SQL code

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)dwhere 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