个人简介
偶尔有空上来看看
文章分类
文章存档
最近访客
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： 数据库开发技术
2010-03-24 10:12:24
ms sql server 2005数据库
假设有一个表，sql语句如下：
create table [dbo].[scan](
[km] [int] null,
[kh] [int] null,
[cj] [int] null
) on [primary]
[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
(
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 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
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()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．
另外几下函数：
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．
dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 ．
lag（arg1,arg2,arg3):
arg1是从其他行返回的表达式
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。
arg3是在arg2表示的数目超出了分组的范围时返回的值。
使用的方法与row_number() 是一样的。（）
给主人留下些什么吧！~~