SQL2005新增函数

SQL2005新增函数

SQL2005新增了几个结果集行号、排名、分组等函数，给我们带来了很大的方便。

 

1. ROW_NUMBER函数
    返回结果集分区内行的序列号。SQL表是基于集合的，没有像DBF，ACCESS这样的记录行的概念。ROW_NUMBER函数返回行号不是数据表的物理行号，而是结果集分区内行的序列号。

    如：SELECT ROW_NUMBER() OVER (ORDER BY ProdCode) AS rownum, * FROM t_Product

   

      返回基于列ProdCode排序集合的行号。利用ROW_NUMBER 及 OVER排序子句，我们可以实现数据分页功能，而以前要比较复杂的代码才能实现。

SELECT   TOP ( 10 ) prodcode,name  FROM  (   SELECT  prodcode,name, ROW_NUMBER()  OVER  ( ORDER   BY  prodcode)  AS  ROW_Number  FROM  t_product)  as  T  WHERE  Row_Number  >  (( 5   -   1 )  *   10 ) 

     这条语句显示产品表的第5页 ，每页10行记录。

 

2. 分组 NTITLE

     NTITLE函数将指定数据集划分成N个组，分组时由Orderby指定排序列。如将产品表划分成10个组：

Select   prodcode,name,ProdTypeCode,NTile( 10 )  OVER ( ORDER   BY  ProdTypeCode)  AS  NTile  From   t_product

      

3. RANK、DENSE_RANK排名函数

     排名函数很容易实现诸如销售排名报表这样的功能，同ROW_NUMBER、NTITLE一样需要指定OVER 排名窗口函数，确定行集的分区和排序。如实现按业务员的销量排名表。

Select   prodid, Sum (quantity)  as  Total,Rank()  OVER ( ORDER    BY   Sum (quantity)  desc )  AS  Rank      From    secontdetl    Group   by  prodid

     运行结果如下：

 

     DENSE_RANK与RANK不同的是返回指定元组在指定集中的排名（排名从 1 开始），但排名号不间断。即如果有2个并列第1名，那么RANK函数第3行记录将是排名3，而DENSE_RANK是2.