SQL Over

与over函数结合的几个函数

create table #tab(A varchar(8), B varchar(8))
insert into #tab
select 'A1', 'B1' union all
select 'A1', 'B2' union all
select 'A1', 'B3' union all
select 'A2', 'B4' union all
select 'A2', 'B5' union all
select 'A2', 'B6' union all
select 'A3', 'B7' union all
select 'A3', 'B8' union all
select 'A3', 'B9'

 

row_number() over() :

select row_number()over(order by A) as id ,* from #tab;

 

partition by:

按A分组，按A排序

select row_number()over(order by A) as id,
*,
row_number()over(partition by A order by A)Num
from #tab;

按A分组，组内按B倒序

select row_number()over(order by A) as id,
*,
row_number()over(partition by A order by B desc)Num
from #tab;

rank()over()

http://www.cnblogs.com/mycoding/archive/2010/05/29/1747065.html

select *,rank()over(order by A)from #tab;

select *,rank()over(partition by A order by A) from #tab;

select *,rank()over(partition by A order by B) from #tab;

也可以按多列分组，具体的看链接。

dense_rank()over()

select *,dense_rank()over(order by A)from #tab;

sum() over():

create table #tab(A varchar(8), B varchar(8),C int)
insert into #tab
select 'A1', 'B1' ,10 union all
select 'A1', 'B2' ,10union all
select 'A1', 'B3' ,10 union all
select 'A2', 'B4' ,20 union all
select 'A2', 'B5' ,20union all
select 'A2', 'B6' ,20union all
select 'A3', 'B7' ,30union all
select 'A3', 'B8',30 union all
select 'A3', 'B9',30

select *,sum(C)over(partition by A)from #tab;

select *,sum(C)over()from #tab;

http://hi.baidu.com/s__wind/item/a20107fa4eb6631ca6298858

http://www.cnblogs.com/85538649/archive/2011/08/13/2137370.html

http://www.cnblogs.com/lanzi/archive/2010/10/26/1861338.html