SQL Server 2000中的Row_Number模拟

带刀守卫

我有一个示例输入表
" Y$ R9 Q4 p& I6 D0 M4 A2 dDeclare  @input TABLE(Name VARCHAR(8))
4 A  P5 Y" }3 r7 IINSERT INTO @input(Name)  values('Aryan')
INSERT INTO @input(Name)  values('Aryan')
INSERT INTO @input(Name)  values('Joseph')
, Y# L7 K# }; {9 M! c* ]INSERT INTO @input(Name)  values('Vicky')
" d1 H2 W5 z" `* l1 KINSERT INTO @input(Name)  values('Jaesmin')
INSERT INTO @input(Name)  values('Aryan')
INSERT INTO @input(Name)  values('Jaesmin')
INSERT INTO @input(Name)  values('Vicky')
INSERT INTO @input(Name)  values('Padukon')
INSERT INTO @input(Name)  values('Aryan')
! K" b& t" z; u' dINSERT INTO @input(Name)  values('Jaesmin')
7 v) h1 k7 A: \! }& i$ O7 F, A/ WINSERT INTO @input(Name)  values('Vick')
INSERT INTO @input(Name)  values('Padukon')
: B$ t( i: X; tINSERT INTO @input(Name)  values('Joseph')
! N! H- n8 A* Q( o$ XINSERT INTO @input(Name)  values('Marya')
% O! l1 o  ]* s1 ?: @INSERT INTO @input(Name)  values('Vicky')
我也有一个理货桌子如下
declare @t table(n int)
' W: q- _, r( Q$ Pinsert into @t select 1 union all select 2 union all
% p5 B9 ^- b% H; n8 }select 3 union all select 4 union all select 5 union all
9 x# A- h# v; P$ Q) Wselect 6 union all select 7 union all select 8 union all
& b) B; b" X5 ]- v. r" |( Lselect 9 union all select 10 union all select 11 union all
  p7 W9 L4 l- b$ U& aselect 12 union all select 13 union all select 14 union all
select 15 union all select 16 union all select 17 union all
select 18 union all select 19 union all select 20
在Sql Server 2005中，如果我这样做
0 E: V1 A0 L3 f2 o' d5 K( W( h3 E Select rn, name from (
    select ROW_NUMBER()over (order by Name) as rn , * from @input) x
& I) c, D  D: d+ L5 @6 W; X8 o3 q    where rn % 2  0
; B; C7 F" v7 ?& s+ r我得到的输出为
rn  name
7 ]9 O7 l, Y  ]1   Aryan
" P% c+ `$ @. J5 T3   Aryan
/ Y) N' Z8 v  j5   Jaesmin
( f; k# h1 r8 V8 l) F7   Jaesmin
6 p. k1 k- C9 U1 v1 L9   Joseph
11  Padukon
13  Vick
15  Vicky
我只能使用Sql Server2000。如何获得相同的输出？
我尝试过
SELECT name, (SELECT COUNT(*) FROM @input AS i2 WHERE i2.Name 但是输出错误
name    rn
Aryan   4
. M& x: B5 Z& b9 f( jAryan   4
Joseph  9
, V% V3 {* F6 n* W. \Vicky   16
8 d0 `& E/ z' \# F! v) bJaesmin 7
+ o* s/ I  u6 L' o: }Aryan   4
) u' E% ~# o0 \% K; mJaesmin 7
Vicky   16
Padukon 12
" X- v& P% x% `, g! u( ^% ?4 xAryan   4
( L9 m; c5 g7 S6 KJaesmin 7
Vick    13
" n1 D- u/ `) `" E, oPadukon 12
6 y$ n& y1 Q! {8 M! [$ z' M8 O; T/ JJoseph  9
Marya   10
Vicky   16
               
2 Q9 J* g+ H- i0 y, Y解决方案:
, l9 {9 P: b* f7 G               
- f+ V" m- p: _( W
0 t/ h5 k8 i5 T+ N# G3 K+ @
, J9 F* A- s1 s; ?                使用此查询：
SELECT t1.name, t.n
FROM
(
6 B- H3 \5 L1 v( |0 Q8 X) h    SELECT a.name, a.c, (SELECT COUNT(*) FROM @input AS i2 WHERE i2.Name  t1.rn - t1.c
* `1 }% k0 h7 G9 s它产生所需的输出：
name     n
-------- -----------
Aryan    1
Aryan    2
' {. ~+ L* ]( Y$ D6 iAryan    3
Aryan    4
8 l# H# p) C. U5 F7 V6 i/ [, rJaesmin  5
Jaesmin  6
Jaesmin  7
Joseph   8
Joseph   9
Marya    10
Padukon  11
+ o0 o# p& w& R( G: O- t5 RPadukon  12
Vick     13
9 m, X8 @1 N4 w% S7 o: c% z( C! KVicky    14
Vicky    15
1 e+ B5 |! p) SVicky    16