窗口函数和Rank()的妙用

原创 2007年10月12日 15:29:00

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

现有如下的表结构:                                        最后的结果应该为:
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 

PostgreSql聚合函数二---聚合函数,分析函数和窗口函数

PostgreSql的窗口函数使用 文档中涉及的表的结构和数据: 1. 表emp_detail: create table emp_detail(  empno integer,  enam...
  • chuan_day
  • chuan_day
  • 2015年04月01日 11:26
  • 3725

MySQL 以及 Python 实现排名窗口函数

MySQL 以及Python 实现排名窗口函数一例。
  • yueliangdao0608
  • yueliangdao0608
  • 2014年05月14日 09:16
  • 3212

spark 窗口函数row_number练习以及用spark core实现

常用于对数据进行分组并取每个分组中的TopN数据。 示例数据如下: class1 90 class2 56 class1 87 class1 76 class2 88 c...
  • a11a2233445566
  • a11a2233445566
  • 2017年01月23日 16:59
  • 1491

在MFC中从一个线程工作函数中向窗口发送消息(this指针的妙用)

前一段时间使用MFC写程序的时候,为了实现从一个窗口向另一个窗口发送消息,使用过下面两种方法 /*方法一:通过用SDK的标准API来查找其他对话框窗口返回句柄,并且发送信息 HWND hWnd; ...
  • gukesdo
  • gukesdo
  • 2011年10月19日 10:30
  • 7787

在MFC中从一个线程工作函数中向窗口发送消息(this指针的妙用) .

前一段时间使用MFC写程序的时候,为了实现从一个窗口向另一个窗口发送消息,使用过下面两种方法 /*方法一:通过用SDK的标准API来查找其他对话框窗口返回句柄,并且发送信息 HWND hWnd...
  • chunfengdeyiding
  • chunfengdeyiding
  • 2014年05月10日 15:15
  • 491

Hive分析窗口函数(二) NTILE,ROW_NUMBER,RANK,DENSE_RANK

本文中介绍前几个序列函数,NTILE,ROW_NUMBER,RANK,DENSE_RANK,下面会一一解释各自的用途。 Hive版本为 apache-hive-0.13.1 注意: 序列函...
  • mlljava1111
  • mlljava1111
  • 2015年11月05日 19:52
  • 270

EVALUATE宏表函数妙用

  • 2013年03月08日 16:05
  • 89KB
  • 下载

oracle_排列rank()函数

  • 2012年07月18日 19:44
  • 38KB
  • 下载

Mysql 查询实现成绩排名,相同分数名次相同,类似于rank()函数

近日系统要实现总分成绩排名,而且相同分数的学生排名要一样,在网上搜了一圈,没有找到合适的方法,只能靠自己实现了,这里提供两种方法1、sql查询实现测试如下:mysql> select * from s...
  • a56508820
  • a56508820
  • 2015年11月05日 15:12
  • 12189

利用rank函数实现自动排序

利用rank函数实现自动排序!
  • kevinhg
  • kevinhg
  • 2011年03月09日 13:27
  • 9415
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:窗口函数和Rank()的妙用
举报原因:
原因补充:

(最多只允许输入30个字)