SQL2005之后MS SQL版本 T-SQL 新增语法、函数（一）

       最近在项目需要编写一些复杂的sql语句，而且有的还对效率的要求比较高，所以用到了SQL2005之后MS SQL版本新增的一些函数，也由此觉得有必要对这些新增的函数、语法进行全面的学习一下。

       今天首先就说一下，over子句吧。

       不过说over子句之前得先说说排名函数。

       排名函数主要包括四个函数：ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE(N），其适用语法如下： 排名函数 + over （order by column），

其中ROW_NUMBER()使用的比较频繁，主要用于数据库分页上。而这四个函数的主要区别为：

       1）ROW_NUMBER()排序之后无重复的“名次”，排序后的数据诸如：1,2,3,4,5,6,7,8,9,10

       2）RANK()和DENSE_RANK()排序之后都有重复数据，但其排序后的数据分别如1,2,2,4,4,4,7,8,9和1,2,2,3,4,4,4,5,5

       3)  NTILE(N)是指将数据排序后分成N组，每条数据都会隶属于某一个组。

       下面举例说明一下排序函数的区别：

       create table test(name nvarchar(20),age int,gender nvarchar(10),[weight] decimal)

      

 insert into test(name,age,gender,[weight]) values('hacker',27,'male',77),('jack',22,'male',65),('rose',23,'female',50),('lily',26,'female',47),('frank',15,'male',45),('mary',12,'female',33),('john',20,'male',54),('larry',8,'female',20),('jackchen',50,'male',66),('hack',15,'male',40),('kitty',20,'female',50),('honey',27,'female',52),('sam',27,'male',68),('sue',27,'male',70)

 

       T-SQL语句：

       select age,row_number() over (order by age desc) as [row_number() age],rank() over (order by age desc) as [rank age],
       dense_rank() over (order by age desc) as [dense_rank age],ntile(4) over (order by age desc) as [ntile age] from test

       结果如下：

            

       接下来看一下over子句，语法如下：

       

排名函数 + < OVER_CLAUSE > :: =     OVER ( [ PARTITION BY value_expression , ... [ n ] ]            <> ) 聚合函数 + < OVER_CLAUSE > :: =     OVER ( [ PARTITION BY value_expression , ... [ n ] ] )

 

       over子句只能和“排名函数”和“聚合函数”一起使用。而over子句有两种形式：partition by和order by（只能和排名函数一起使用）。

       1.partition by ：先分组后执行聚合函数或排名函数

       2.order by ：只能与排名函数一起使用，在上面介绍排序函数时已经介绍

       下面还是举例还说明partition by，这里主要展示聚合函数 +PARTITION BY使用

       T-SQL语句：

        select gender,
        (select sum([weight]) from test) as TotalWeight,
        sum([weight]) over(partition by gender) as SubTotalWeight,
        (select avg([weight]) from test) as AverageWeight,
        avg([weight]) over(partition by gender) as SubAverageWeight,
        (select max([weight]) from test) as MaxWeight,
        max([weight]) over(partition by gender) as SubMaxWeight
        from test 

        结果如下：

       

 

 

转载于:https://www.cnblogs.com/hacker012/archive/2011/06/13/2079670.html