现有一数据表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