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
