现有一数据表test，结构如下
| studentname | coursename | mark |
| a | a | 80 |
| a | b | 60 |
| a | c | 30 |
| b | a | 40 |
| b | b | 50 |
| b | c | 58 |
| c | a | 60 |
| c | b | 70 |
| c | c | 88 |
| d | a | 90 |
| d | b | 70 |
| d | c | 88 |
1 查出每门功课的前一名（并列名次问题）
select * from (
select *,ROW_NUMBER() over(partition by coursename order by mark desc) ranks
from test_a
) a
where a.ranks=1
partition by ：分区；按coursename分区，依据mark排序 即可得到每门课程的第一名，同理改变条件可获得前三名、前五名等。
2 查出至少两门功课达到60分的学生
select a.studentname from (
select studentname,coursename
from test_a where mark>60
) a
group by a.studentname
having count(a.coursename)>=2