SQL server 2005中新增函数及操作

1 SQL server 2005中新增的排序函数及操作
SELECT

YEAR(BirthDate),

ROW_NUMBER() OVER(ORDER BY YEAR(BirthDate)) AS 'RowNumber',/* 按年产生一个唯一的序号 */

ROW_NUMBER() OVER(PARTITION BY YEAR(BirthDate) ORDER BY BirthDate) AS RowNumberPartition, /* 每年产生一个唯一的序列 */

RANK() OVER(ORDER BY YEAR(BirthDate)) AS 'Rank', /* 产生一个非紧密排名 */

DENSE_RANK() OVER(ORDER BY YEAR(BirthDate)) AS 'Dense_Rank', /* 产生一个紧密排名 */

NTILE(10) OVER(ORDER BY BirthDate DESC) AS 'ntile' /* 将结果分成10个组 */

FROM HumanResources.Employee

ORDER BY BirthDate

看看新的排序函数如何解决SQL server 2000中不方便解决的问题

--按BirthDate排序，取第10条到20条的数据 (这一定是最激动人心的新特性，哈哈)

SELECT BirthDate FROM

(

SELECT

ROW_NUMBER() OVER(ORDER BY BirthDate) AS a,BirthDate

FROM HumanResources.Employee

) AS a

WHERE a BETWEEN 10 AND 20

--将数据分成十份，取第三份

SELECT

BirthDate

FROM

(

SELECT

NTILE(10) OVER(ORDER BY BirthDate DESC) AS a,BirthDate

FROM HumanResources.Employee

) AS a

WHERE a = 3再来看看如何用新的排序函数解决以前在SQL server 2000中的问题

-- 出生的员工最多的一年出生多少员工（有点）

/* SQL server 2000 */

SELECT MAX(a)

FROM

(

SELECT COUNT(EmployeeID) AS a FROM HumanResources.Employee GROUP BY YEAR(BirthDate)

) AS a

/* SQL server 2005 */

SELECT MAX(a)

FROM

(

SELECT

ROW_NUMBER() OVER(PARTITION BY YEAR(BirthDate) ORDER BY BirthDate) AS a

FROM HumanResources.Employee

) AS a

-- 出生日期涵盖了多少年

/* SQL server 2000 */

SELECT COUNT(DISTINCT YEAR(BirthDate)) FROM HumanResources.Employee

/* SQL server 2005 */

SELECT MAX(a)

FROM

(

SELECT

DENSE_RANK() OVER(ORDER BY YEAR(BirthDate)) AS a

FROM HumanResources.Employee

) AS a

-- 显示前10%的数据

/* SQL server 2000 */

SELECT TOP 10 Percent BirthDate FROM HumanResources.Employee

/* SQL server 2005 */

SELECT

BirthDate

FROM

(

SELECT

NTILE(10) OVER(ORDER BY BirthDate DESC) AS a,BirthDate

FROM HumanResources.Employee

) AS a

WHERE a = 1

posted on 2008-02-13 16:32 小寒阅读(457) 评论(5) 编辑收藏所属分类: 数据库设计UNION技巧

#1楼 2008-02-20 10:47 fenglei801221 [未注册用户]

太经典了，准备收藏
回复引用

#2楼 [楼主] 2008-02-21 09:08 小寒

赫赫，上次没帮上忙，不好意思
最近倒是比较闲，项目结束了，赫赫
回复引用查看

#3楼 2008-03-07 11:29 耀威

不错,收藏了
回复引用查看

#4楼 [楼主] 2008-05-05 13:04 小寒

每个分类取最新的几条的SQL实现
问题详细描述如下：

比如，假设我们有下面这样结构的一张表，这张表的数据量非常巨大。

CREATE TABLE table1( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](128) NOT NULL, [class] int not null, [date] datetime not null)class 表示分类编号。分类数不固定，至少有上千种分类
date 表示该条记录被更新的时间
我们现在想获得每个分类最新被更新的5条记录。

解决方案

select id,name,class,date from(select id,name,class,date ,row_number() over(partition by class order by date desc)as rowindex from table1) awhere rowindex <= 5

解决方案简单说明：

这个解决方案的关键就是使用了SQL 2005 的 ROW_NUMBER 这个全新的函数。

ROW_NUMBER ( ) 函数的语法如下：
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

OVER 子句中的 PARTITION BY 将结果集分为多个分区。
OVER 子句中的 ORDER BY 将对 ROW_NUMBER 进行排序。

回复引用查看