oracle/mysql 分析函数

row_number() over(PARTITION BY channelcorpid ORDER BY channel_id

oracle 分析函数

一张表名为student的学生成绩分析表,字段u_id,course,score,分别为学生id，课程，分数，求每科成绩的前五名的u_id,course,score

SELECT uid,course ,score
  FROM (SELECT b.course ,
               b.score ,
               b.uid,
               IF(@course  = b.course , @rank := @rank + 1, @rank := 1) AS rank,
               @course  := b.course
          FROM (SELECT course , score, uid
                  FROM student
                 ORDER BY course  ,score DESC) b,
               (SELECT @rownum := 0, @course  := NULL, @rank := 0) a) RESULT
where rank<=5;