关闭

与over函数结合的几个函数介绍

144人阅读 评论(0) 收藏 举报
分类:

下面以班级成绩表t2来说明其应用

t2表信息如下:
cfe               74
dss               95
ffd               95
fda               80
gds               92
gf                99
ddd               99
adf               45
asdf              55
3dd               78

select * from                                                                      
                                                                              
    select name,class,s,rank()over(partition by class order by s desc) mm from t2
                                                                              
    where mm=1;
得到的结果是:
dss               95        1
ffd               95        1
gds               92        1
gf                99        1
ddd               99        

注意:
    1.在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果;
select * from                                                                      
                                                                              
    select name,class,s,row_number()over(partition by class order by s desc) mm from t2
                                                                              
    where mm=1;
       95         --95有两名但是只显示一个
       92        1

       99        1 --99有两名但也只显示一个


select name,class,s, sum(s)over(partition by class order by s desc) mm from t2 --按班级对成绩进行排序并累加求和
dss               95        190  --由于两个95都是第一名,所以累加时是两个第一名的相加
ffd               95        190 
fda               80        270  --第一名加上第二名的
gds               92        92
cfe               74        166
gf                99        198
ddd               99        198
3dd               78        276
asdf              55        331
adf               45        376

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:37684次
    • 积分:515
    • 等级:
    • 排名:千里之外
    • 原创:1篇
    • 转载:53篇
    • 译文:4篇
    • 评论:0条