row_number() over(order by) 与count(1)

2005中的  row_number() over(order by) 给我们带来了什么 例子比较

借用无枪的狙击手 与 昨夜小楼

declare @t table(a char(2),b char(2),c char(2),d char(2))
insert @t select 'a1','b1','c1','d'
insert @t select 'a1','b1','c2','d'
insert @t select 'a1','b1','c3','d'
insert @t select 'a1','b2','c4','d'
insert @t select 'a1','b2','c5','d'
insert @t select 'a2','b3','c6','d'

select a,b,c,
(select count(1) from @t where a.a = a and a.b = b and c <= a.c) as count
from @t a

/*


a    b    c    count      
---- ---- ---- -----------
a1   b1   c1   1
a1   b1   c2   2
a1   b1   c3   3
a1   b2   c4   1
a1   b2   c5   2
a2   b3   c6   1

（所影响的行数为 6 行）

--原始数据：@TT
declare @TT table(a varchar(2),b varchar(2),c varchar(2),d varchar(1))
insert @TT
select 'a1','b1','c1','d' union all
select 'a1','b1','c2','d' union all
select 'a1','b1','c3','d' union all
select 'a1','b2','c4','d' union all
select 'a1','b2','c5','d' union all
select 'a2','b3','c6','d'

/*
partition by a,b -> 分组
order by c ->分组编号顺序，如果顺序不敏感，可如上例 order by a
*/
select *, id = row_number() over (partition by a,b order by c) from @TT

/*
a    b    c    d    id
---- ---- ---- ---- --------------------
a1   b1   c1   d    1
a1   b1   c2   d    2
a1   b1   c3   d    3
a1   b2   c4   d    1
a1   b2   c5   d    2
a2   b3   c6   d    1

一目了然呵呵