over (partition by)

班级表

点击(此处)折叠或打开

1. select * from class;

求各个班级的第一名分数。

点击(此处)折叠或打开

1. select *
   
2.   from (select name,
   
3.                class_no,
   
4.                score,
   
5.                rank() over(partition by class_no order by score desc) rank
   
6.           from class)
   
7.  where rank = 1

其中子查询出来的数据如下：

点击(此处)折叠或打开

1. (select name,
   
2.                class_no,
   
3.                score,
   
4.                rank() over(partition by class_no order by score desc) rank
   
5.           from class)

rank()和dense_rank()的区别是：
   --rank()是跳跃排序，有两个第二名时接下来就是第四名
   --dense_rank()l是连续排序，有两个第二名时仍然跟着第三名


row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29990276/viewspace-1660361/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29990276/viewspace-1660361/