窗口函数和Rank()的妙用

在现实生活中,下面的情况是不是经常发生:
在每次期末考试中,规定每个班级成绩最高(即第一名)的学生将获得三好学生的荣誉,那么怎样取出成绩最高学生的学号、姓名、成绩等相关信息呢?
    为了便于说明问题,先把表的一些相关信息予以介绍:

现有如下的表结构:                                        最后的结果应该为:
sid    sname        sscore      sclass                  sid        sscore    sclass
----   ---------    ------      ------                  ----       ------    ------
 1     zhang san       90       class1                    1          90       class1
 2     li si           85       class1                    3          90       class1
 3     zhang san       90       class1                    4          100      class2
 4     zhang san       100      class2                    7          null     class3
 5     li si           90       class2                    8          null     class3
 6     zhang san       90       class2
 7     zhang san       null     class3
 8     zhang san       null     class3
其中sid为主键,现在我想首先取出sname为zhang san的数据,然后按照sclass进行分组,最后取出sscore最大的所对应的sid和sclass等相关信息(tips:所有字段中除了sscore是number型之外,其余全为varchar型,另外之所以要把数据凑成这样是为了测试重复数据)。

下面首先给出正确的解答:
1  select e.sid,e.sname,max(e.sscore) over (partition by sclass) sscore
2  from  (
3       select sid,sname,sclass,sscore,
4       rank() over(partition by sclass order by sscore desc) as rn
5       from   chunting.dbo.student_score
6       where  sname='zhang san'
7         )  e
8  where  e.rn=1
(以上语句在SQL Server 2005中调试通过)
也许一般人当看到这个问题的第一反应便是用group by来实现,这样就掉进了错误的陷阱中,因为由于在sql语句中如果使用了group by那么sid字段就无法直接取出,因为它既不是group by字段,也不能被聚合函数所修饰,所以只能用partition by(以后如果遇到同样的问题即:既要取分组后的最大值,又要取像sid这样的字段,就可以仿照上面的写法来解决,这一招很是实用,希切记!)。如果在数据库中没有重复的数据那上面所说的就可以解决问题了,但在现实中往往是相反的,例如在一个班级中同时有多人考取第一名的情况时有发生,这就要采取下面的方法,即利用rank() over(partition by sclass order by sscore desc) as rn它的意思就是首先根据sclass进行分组然后根据sscore进行排序并且赋值以"序号"(rank的好处在于它把sscore值相同的行赋以相同的"序号",这就为以后把所有第一名都取出来提供了保证,这也是它与row_number的最大区别,row_numer是根据分组然后给每行赋以不同的"序号"),为了便于说明情况下面予以展示这两种语句的运行结果(即上面sql语句的3456行):

3456的运行结果为:                               把rank改为row_number其余不变(3456)的结果为:
sid    sname     sclass   sscore   rn        sid   sname     sclass   sscore   rn  
---   --------   ------   ------   --        ---   --------  ------   ------   ---
1     zhang san  class1    90       1         1    zhang san   class1   90      1
3     zhang san  class1    90       1         3    zhang san   class1   90      2
4     zhang san  class2    100      1         4    zhang san   class2   100     1
6     zhang san  class2    90       2         6    zhang san   class2   90      2
7     zhang san  class3    null     1         7    zhang san   class3   null    1
8     zhang san  class3    null     1         8    zhang san   class3   null    2 

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭