Sqlserver 2005 排名函数

sqlserver排名函数：

  ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
  RANK () OVER ([<partition_by_clause>] <order_by_clause>)
  DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
  NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)

  PARTITION的利用

  使用排名函数时候 不用排序...节约时间

 

1、 ROW_NUMBER ()

例：

SELECT ProductID, Name, Price, 
       ROW_NUMBER() OVER(ORDER BY Price DESC) As PriceRank
FROM Products
  特点 忽略重复的情况，序列 直接上升 结果：
ProductID	Name	                 Price	     PriceRank
8	        Desk	                         495.0000	1
10	        Executive Chair	 295.0000	2
9	        Chair	                 125.0000	3
5	        Mouse	                 14.9500  	4
6	        Mouse      	         9.9900    	5
11	        Scissors	                 9.9900	        6
4	        Stapler                      7.9500	        7
3	        Binder	                 1.9500	        8

2、RANK() 和DENSE_RANK () 
  他们考虑到了重复列的影响
 如果 是RANK() 结果是：
ProductID	Name	                 Price	     PriceRank
8	        Desk	                         495.0000	1
10	        Executive Chair	 295.0000	2
9	        Chair	                 125.0000	3
5	        Mouse	                 14.9500  	4
6	        Mouse      	         9.9900    	5
11	        Scissors	                 9.9900	        5
4	        Stapler                      7.9500	        7
3	        Binder	                 1.9500	        8

如果 是DENSE_RANK() 结果是：
ProductID	Name	                 Price	     PriceRank
8	        Desk	                         495.0000	1
10	        Executive Chair	 295.0000	2
9	        Chair	                 125.0000	3
5	        Mouse	                 14.9500  	4
6	        Mouse      	         9.9900    	5
11	        Scissors	                 9.9900	        5
4	        Stapler                      7.9500	        6
3	        Binder	                 1.9500	        7

区别很明显吧 看看 倒数第三行的不同 

3、 NTILE () 这个函数 很厉害 
 它会将结果 利用序号分组 如下 看结果体会 ：
SELECT ProductID, Name, Price, 
       NTILE (3) OVER(ORDER BY Price DESC) As PriceRank
FROM Products
结果是 ：
ProductID	Name	                 Price	     PriceRank
8	        Desk	                         495.0000	1
10	        Executive Chair	 295.0000	1
9	        Chair	                 125.0000	1
5	        Mouse	                 14.9500  	2
6	        Mouse      	         9.9900    	2
11	        Scissors	                 9.9900	        2
4	        Stapler                      7.9500	        3
3	        Binder	                 1.9500	        3

4、  关于partition  看如下sql
SELECT ProductID, Name, Price, 
   ROW_NUMBER () OVER(PARTITION BY Name ORDER BY Price DESC) As PriceRank
FROM Products

结果是：
ProductID	Name	                 Price	     PriceRank
8	        Desk	                         495.0000	1
10	        Executive Chair	 295.0000	1
9	        Chair	                 125.0000	1
5	        Mouse	                 14.9500  	2
6	        Mouse      	         9.9900    	2
11	        Scissors	                 9.9900	        1
4	        Stapler                      7.9500	        1
3	        Binder	                 1.9500	        1

  利用此函数 可以顺利解决这样的问题 
  现在商城要做一个商品排名，挑选出最好买的50中商品显示在首页，但是有可能某个牌子的一系列商品都卖得比较好，你不想看到的商品排名是如下的样子吧：
 商品排名：
 top 50
 名次             商品名称                    出产厂商
 1                 七度空间 日用             恒安集团
 1                 七度空间 夜用             恒安集团
 1                 七度空间 有翅膀          恒安集团
 1                 七度空间  XXX            恒安集团
 1                 七度空间  XXX            恒安集团
.........
.........
这样就逊掉了嘛 利用PARTITION 可以顺利解决这个问题

5、使用排名函数时候 不用排序...
  其实使用排名函数的时候 不可避免的使用order 这个时候 SQL执行期间会使用sort 这个比较浪费时间
但是 有可能 你不需要这样的排序 可以避免它， 如下：
 SELECT A.ProductID, A.Name, A.Price, 
   ROW_NUMBER () OVER(ORDER BY A.const) As PriceRank
FROM (SELECT ProductID, Name, Price, 1 as const
FROM Products) as A
  利用一个const字段 屏蔽了 执行中的 sort过程