Sql Server2005对t-sql的增强之排名函数
来源:互联网 发布:淘宝卖家怎么修改中评 编辑:程序博客网 时间:2024/05/20 00:11
Sql Server2005中新增加了4个排名函数:ROW_NUMBER, RANK, DENSE_RANK, NTILE;大家一定已经对ROW_NUMBER非常熟悉了,所以我从最后一个NTILE开始分析。
NTILE在msdn中的解释是:将有序分区中的行分发到指定数目的组中。各个组有编号,编号从一开始。对于每一个行,NTILE 将返回此行所属的组的编号。不知道大家是不是一下子就能看懂这个解释,反正我是结合解释自己写了例子才弄明白的。
准备脚本,我们创建一个简单的3列表,三列分别是id,categoryId,和name,如下:
----------------------------分割线-------------------------------
下面看RANK和DENSE_RANK这对兄弟函数,这对函数要比NTITL容易理解一些。MSDN对RANK的解释:返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。MSDN上对DENSE_RANK的解释是:返回结果集分区中行的排名,在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。下面我用一个例子来说明一下,用结果说明他们的差别:
select脚本,可以得到如下的结果
drop table student_class_grade;
GO
create table student_class_grade
(
student_id int, --学生id
class_no int, --班级编号
grade int --成绩
);
GO
INSERT INTO student_class_grade VALUES(1,1,90);
INSERT INTO student_class_grade VALUES(2,1,85);
INSERT INTO student_class_grade VALUES(3,1,80);
INSERT INTO student_class_grade VALUES(4,1,80);
INSERT INTO student_class_grade VALUES(5,1,90);
INSERT INTO student_class_grade VALUES(6,1,75);
INSERT INTO student_class_grade VALUES(7,1,89);
INSERT INTO student_class_grade VALUES(11,2,90);
INSERT INTO student_class_grade VALUES(12,2,85);
INSERT INTO student_class_grade VALUES(13,2,80);
INSERT INTO student_class_grade VALUES(14,2,80);
INSERT INTO student_class_grade VALUES(15,2,90);
INSERT INTO student_class_grade VALUES(16,2,75);
INSERT INTO student_class_grade VALUES(17,2,89);
GO
--显示各个班级学生的成绩排名
SELECT student_id
,class_no,grade
,'名次' = RANK() OVER(PARTITION BY class_no ORDER BY grade desc)
FROM student_class_grade
GO
SELECT student_id
,class_no,grade
,'名次' = DENSE_RANK() OVER(PARTITION BY class_no ORDER BY grade desc)
FROM student_class_grade
分别执行下面两个
可以看到1班同学的排名依次是1,1,3,4有了并列第一之后第二名的排序就是3了。
如下是DENSE_RANK的执行结果:
可以看到排名依次是1,1,2,3 … 当出现两个并列第一之后,第二名的排名是2,而非RANK中的3.所以我们在给学生成绩排名时可以用DENSE_RANK而不是RANK。
---------------------------分割线-------------------
最后要介绍的是ROW_NUMBER这个函数为我们分页提供了便利。我们可以结合CTE(通用表表达式)使用,如下例子
SELECT student_id,class_no,grade,rn = ROW_NUMBER() OVER(ORDER BY student_id ASC)
FROM student_class_grade
WHERE 0=0 --可以在此处加一些过滤条件,这样下面的分页的sql中就都不需要加条件了
)
--获得第-10条的数据
SELECT student_id,class_no,grade FROM CTE_rn WHERE rn BETWEEN 6 AND 10;
SELECT totalCn = COUNT(*) FROM student_class_grade WHERE 0=0
ROW_NUMBER函数可以在取每个分类的前n条记录时很有用。
例如:
create table student(
id int not null,
name varchar(20),
grade int,
class int --班级
)
GO
--以下select语句返回每班级前3名的学生
WITH student_rn AS
(select id,name,class,grade, 排名 =ROW_NUMBER() OVER(PARTITION BY class ORDER BY grade DESC) FROM student)
SELECT id,name,class,grade, 排名 FROM student_rn
WHERE 排名 <= 3
全文结束。
if object_id('t_ntile','U') is not null
drop table t_ntile;
GO
create table t_ntile
(
id int unique not null,
categoryId int not null,
name nvarchar(20)
)
go
INSERT INTO t_ntile VALUES(1,1,'A')
INSERT INTO t_ntile VALUES(2,4,'B')
INSERT INTO t_ntile VALUES(3,2,'C')
INSERT INTO t_ntile VALUES(4,1,'D')
INSERT INTO t_ntile VALUES(5,3,'E')
INSERT INTO t_ntile VALUES(6,3,'F')
INSERT INTO t_ntile VALUES(7,2,'G')
INSERT INTO t_ntile VALUES(8,2,'H')
INSERT INTO t_ntile VALUES(9,2,'I')
Go
查询语句如下:
,'ntile value' = NTILE(3) OVER(PARTITION BY categoryId ORDER BY categoryId)
FROM t_ntile
我们给NTITL传的参数是3,即表示一共三组,然后OVER中表达式指定要根据categoryId来分割分组,并要按照categoryId排序。上面的表达式执行结果如下:
- Sql Server2005对t-sql的增强之排名函数
- Sql Server2005对t-sql的增强之排名函数 (转)
- Sql Server2005对t-sql的增强之Cross Apply
- Sql Server2005对t-sql的增强之Cross Apply
- Sql Server2005对t-sql的增强之通用表表达式CTE
- SQL Server2005排名函数
- SQL Server2005 中的十个最重要的T-SQL增强功能
- Sql Server2005 Transact-SQL 排名函数
- Sql Server2005 Transact-SQL 排名函数
- SQL Server 2005对T-SQL的增强之在聚合函数的后面使用over关键字
- 排名函数是SQL Server2005新加的功能
- SQL Server 2008 对 T-SQL 语言的增强
- SQL Server 2008对T-SQL语言的增强
- SQL Server 2008对T-SQL语言的增强
- SQL Server 2008 对 T-SQL 语言的增强
- SQL Server 2008 对 T-SQL 语言的增强
- SQL Server2012 T-SQL对分页的增强尝试
- SQL Server2012 T-SQL对分页的增强尝试
- CEdit 字体大小 只读属性
- a programmer’s geometry
- 数字频率计
- 各种数据库驱动和URL地址信息
- Silverlight 3 Is Released To The Web
- Sql Server2005对t-sql的增强之排名函数
- linux远程登录免password
- 【WitchHat.cn】信息分级
- 想购物去淘宝123导购看看
- C#经典23个使用技巧
- 控件字体
- String与InputStream相互转换
- 双色球随机号生成程序
- List Control的刷新闪烁