每组前N条记录 SQL

MS SQL SERVER 2005数据库

 

假设有一个表，SQL语句如下：

CREATE TABLE [dbo].[scan](
    [km] [int] NULL,
    [kh] [int] NULL,
    [cj] [int] NULL
) ON [PRIMARY]

    其中km为科目号、kh为考生号、cj为成绩，现对km和kh进行分组，并获得每组前2条记录（按cj从高到低排序）。基本思想是为每组加一个序号列，再用where取序号小于等于2的。SQL语句如下：

select * from
(
    select a.km,a.kh,cj,row_number() over(partition by a.km order by a.km,a.cj desc) n
    from
        (select km,kh,SUM(cj) cj from scan group by km,kh) a
) b where n<=2 order by km, cj desc

 

----------------

Oracle数据库

 

在oracle中有一数据表exam_result（成绩记录表），

表中的一条记录描述了“某个班某个学生某次考试的成绩"

create table EXAM_RESULT
(
  ID      NUMBER(10) not null,                   --主键
  CLASSID NUMBER(10) not null,           --  班级id，关联到班级表
  USERID  NUMBER(10) not null,             --用户id，关联到用户表
  EXAMID  NUMBER(10) not null,             --试卷id，关联到试卷表
  RESULT  NUMBER(3)                              --成绩
)

 

现在要求统计完成了试卷id为1,2,3的成绩的前3名

即完成了试卷id为1的前3名,完成了试卷id为2的前3名,完成了试卷id为3的前3名

 

Sql代码
select * from (  
      select   
        e.classid,   
        e.userid,   
        e.examid,   
        e.result,   
            row_number() over (partition by e.examid order by e.examid, e.result desc) rn  
                from exam_result e   
                        where e.examid in (1,2,3)  
) where rn <= 3 

 

row_number() over的函数是将按e.examid分组，再按e.examid降序排列。这两个字段当然可以不一样。。这个函数与nownum的功能差不多，区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．
另外几下函数：
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．

dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 ．

lag（arg1,arg2,arg3):
arg1是从其他行返回的表达式
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。
arg3是在arg2表示的数目超出了分组的范围时返回的值。

使用的方法与row_number() 是一样的。（参考）