MS-SQL Server2005的几个新函数

ROW_NUMBER 函数 [集合]
CUME_DIST 函数 [集合]
DENSE_RANK 函数 [集合]
PERCENT_RANK 函数 [集合]
RANK 函数 [集合]

1、ROW_NUMBER 函数 [集合]

功能
对每一行分配一个唯一的编号。

语法
ROW_NUMBER ( )

示例
下面的示例返回了一个为每一个 Utah 和 New York 州的职员提供唯一行号的结果集。由于该查询按薪水降序排序，所以第一个行号赋予给了数据集中薪水最高的职员。尽管有两名职员具有相同的薪水，但是并没有解析关联，因为这两名职员被分配了唯一的行号。

SELECT emp_lname, salary, state,
ROW_NUMBER () OVER (ORDER BY salary DESC) "Rank"
FROM employee WHERE state IN ('NY','UT')
下面是结果集：

emp_lname	salary	state	Rank
Shishov	72995.000	UT	1
Wang	68400.000	UT	2
Cobb	62000.000	UT	3
Morris	61300.000	UT	4
Davidson	57090.000	NY	5
Martel	55700.000	NY	6
Blaikie	54900.000	NY	7
Diaz	54900.000	NY	8
Driscoll	48023.690	UT	9
Hildebrand	45829.000	UT	10
...	...	...	...
Lynch	24903.000	UT	19

2、CUME_DIST 函数 [集合]

功能
计算某个值在一组行中的相对位置。它返回介于 0 和 1 之间的小数值。

语法
CUME_DIST ( )

用法
目前在 CUME_DIST 函数中还不允许使用组合排序键。您可以在任何其它秩函数中使用组合排序键。

示例
下面的示例返回一个包含居住在 California 的职员的薪水累计分布的结果集。

SELECT dept_id, emp_lname, salary,
CUME_DIST () OVER (PARTITION BY dept_id
ORDER BY salary DESC) "Rank"
FROM employee
WHERE state IN ('CA')
下面是结果集：

dept_id	emp_lname	salary	Rank
200	Savarino	72300.000	0.333333333333333
200	Clark	45000.000	0.666666666666667
200	Overbey	39300.000	1

3、DENSE_RANK 函数 [集合]

功能
计算一个分区中的值的秩。对于并列的数值，DENSE_RANK 在排名序列中不会留有空位。

语法
DENSE_RANK ( )

示例
下面的示例返回了一个包含 Utah 和 New York 州的职员薪水排位的结果集。尽管在返回的结果集中包含 19 条记录，但只列出了 18 个排位，因为在列表中第 7 名职员与第 8 名具有相同的薪水而并列第 7 位。因为 DENSE_RANK 函数不会在排位中留有空位，所以第 9 名职员的排位为 '8' 而不是 '9'。

SELECT dept_id, emp_lname, salary, state,
DENSE_RANK () OVER (ORDER BY salary DESC) "Rank"
FROM employee
WHERE state IN ('NY','UT')
下面是结果集：

emp_lname	salary	state	Rank
Shishov	72995.000	UT	1
Wang	68400.000	UT	2
Cobb	62000.000	UT	3
Morris	61300.000	UT	4
Davidson	57090.000	NY	5
Martel	55700.000	NY	6
Blaikie	54900.000	NY	7
Diaz	54900.000	UT	7
Driscoll	48023.000	UT	8
Hildebrand	45829.000	UT	9
Whitney	45700.000	NY	10
Guevara	42998.000	NY	11
Soo	39075.000	NY	12
Goggin	37900.000	UT	13
Wetherby	35745.000	NY	14
Ahmed	34992.000	NY	15
Rebeiro	34576.000	UT	16
Bigelow	31200.000	UT	17
Lynch	24903.000	UT	18

4、PERCENT_RANK 函数 [集合]

功能
计算由 ORDER BY 子句定义，在返回的查询中某一行相对于其它行的（小数）位置。它返回介于 0 和 1 之间的小数值。

语法
PERCENT_RANK ( )

示例
下面的示例返回了根据性别列出的 New York 的职员薪水排位结果集。该结果使用百分比数按降序列出排位并按性别分区。

SELECT dept_id, emp_lname, salary, sex,
PERCENT_RANK () OVER (PARTITION BY sex
ORDER BY salary DESC) "Rank"
FROM employee
WHERE state IN ('NY')
下面是结果集：

dept_id	emp_lname	salary	sex	Rank
200	Martel	55700.000	M	0
100	Guevara	42998.000	M	0.333333333
100	Soo	39075.000	M	0.666666667
400	Ahmed	34992.000	M	1
300	Davidson	57090.000	F	0
400	Blaikie	54900.000	F	0.333333333
100	Whitney	45700.000	F	0.666666667
400	Wetherby	35745.000	F	1

5、RANK 函数 [集合]

功能
计算一组值中的一个秩的值。对于出现并列的情况，RANK 会在排名序列中留有空位。

语法
RANK ( )

示例
下面的示例提供了 Utah 和 New York 州的职员薪水降序排位。注意到第 7 名职员与第 8 名具有相同的薪水因而并列第 7 位。接下来的职员的排位为第 9 位而在排位序列中留下了空位（没有第 8 位排位）。

SELECT emp_lname, salary, state,
RANK () OVER (ORDER BY salary DESC) "Rank"
FROM employee WHERE state IN ('NY','UT')
下面是结果集：

emp_lname	salary	state	Rank
Shishov	72995.000	UT	1
Wang	68400.000	UT	2
Cobb	62000.000	UT	3
Morris	61300.000	UT	4
Davidson	57090.000	NY	5
Martel	55700.000	NY	6
Blaikie	54900.000	NY	7
Diaz	54900.000	NY	7
Driscoll	48023.690	UT	9
Hildebrand	45829.000	UT	10
Whitney	45700.000	NY	11
...	...	...	...
Lynch	24903.000	UT	19

6、PIVOT 行列转换

CREATE TABLE E_money (work_no varchar(6),moeny_type varchar(10),num int )

work_no money_type num
100001HKD      100
100001HKD      200
100001USD      300
200020HKD      50
200020RMB      5000
200020USD      500
400078HKD      80
400078HKD      90
400078RMB      800
400078RMB      900
400078USD      90
400078USD      800

--统计每个员工不同币种拥有数
work_no 港币       美元       人民币
10000130030050
200020505005000
4000781708901700

--sql 2000写法
select work_no,
sum(case when moeny_type='HKD' then num else 0 end) as 港币,
sum(case when moeny_type='USD' then num else 0 end) as 美元,
sum(case when moeny_type='RMB' then num else 0 end) as 人民币
from e_money
group by work_no

--2005新增函数
SELECT
work_no,
[HKD] as 港币,
[USD] as 美元,
[RMB] as 人民币
FROM e_money
PIVOT(SUM(num) FOR moeny_type IN([HKD],[USD],[RMB])) AS pvt

7、NTILE(N) 将结果分成N个组

SELECT A,B ,C,MyGroup=NTILE(10) OVER(ORDER BY BirthDate DESC) --将结果分成10个组

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

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