| 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
|
| 上一篇:上一篇文章： MS SQL Server 2008数据库最新版功能介绍 下一篇
下一篇文章： 没有了 |
收藏此文到 百度搜藏|
新浪VIvi|
365key|
Younote|
博采中心|
你好BLOG|
亿友网摘|
和讯网摘| |
|
|
|
|
