函数：排名函数

 

㈠排名函数

⒈RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

功能：返回结果集分区内中每行的排名(不连续)。行的排名是相关行之前的排名数加一。

partition_by_cluause：分区字段。

order_by_cluause：排序字段。

 

示例：

declare @a table

(

    my_group varchar(50),

    my_name varchar(50),

    my_grade int

)

 

insert into @a

select 'g1','a',10 union all

select 'g1','b',20 union all

select 'g1','c',20 union all

select 'g1','d',50 union all

select 'g2','e',20 union all

select 'g2','f',60 union all

select 'g2','g',90

 

select

    my_group,

    my_name,

    my_grade,

    rank =

       rank() over (partition by my_group order by my_grade desc)

from @a

结果：

g1  d   50  1

g1  b   20  2

g 1   c   20  2

g 1   a   10  4

g 2   g   90  1

g 2   f   60  2

g2  e   20  3

 

⒉DENSE_RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

功能：返回结果集分区内中每行的排名(连续)，在排名中没有任何间断。行的排名是相关行之前的排名数加一。

partition_by_cluause：分区字段。

order_by_cluause：排序字段。

 

示例：

declare @a table

(

    my_group varchar(50),

    my_name varchar(50),

    my_grade int

)

 

insert into @a

select 'g1','a',10 union all

select 'g1','b',20 union all

select 'g1','c',20 union all

select 'g1','d',50 union all

select 'g2','e',20 union all

select 'g2','f',60 union all

select 'g2','g',90

 

select

    my_group,

    my_name,

    my_grade,

    rank =

       dense_rank() over (partition by my_group order by my_grade desc)

from @a

 

结果:

g1  d   50  1

g1  b   20  2

g 1   c   20  2

g 1   a   10  3

g 2   g   90  1

g 2   f   60  2

g2  e   20  3

 

⒊ROW_NUMBER ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

功能：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。

partition_by_cluause：分区字段。

order_by_cluause：排序字段。

 

示例：

declare @a table

(

    my_group varchar(50),

    my_name varchar(50),

    my_grade int

)

 

insert into @a

select 'g1','a',10 union all

select 'g1','b',20 union all

select 'g1','c',20 union all

select 'g1','d',50 union all

select 'g2','e',20 union all

select 'g2','f',60 union all

select 'g2','g',90

 

select

    my_group,

    my_name,

    my_grade,

    rank =

       ROW_NUMBER() over (partition by my_group order by my_grade )

from @a

 

结果：

g 1     a      10    1

g1    b     20    2

g 1     c     20    3

g1    d     50    4

g2    e      20    1

g 2     f      60    2

g 2     g     90    3

 

⒊NTILE(integer_expression) OVER ( [ < partition_by_clause > ]

< order_by_clause > )

功能：将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号。

partition_by_cluause：分区字段。

order_by_cluause：排序字段。

 

示例：

declare @a table

(

    my_group varchar(50),

    my_name varchar(50),

    my_grade int

)

 

insert into @a

select 'g1','a',10 union all

select 'g1','b',20 union all

select 'g1','c',20 union all

select 'g1','d',50 union all

select 'g2','e',20 union all

select 'g2','f',60 union all

select 'g2','g',90

 

select

    my_group,

    my_name,

    my_grade,

    rank =

       NTILE(2) over (partition by my_group order by my_grade )

from @a

 

结果：

g 1     a      10    1

g1    b     20    1

g 1     c     20    2

g1    d     50    2

g2    e      20    1

g 2     f      60    1

g 2     g     90    2