Sql server 2000中的Row_Number模拟

Question

我有一个示例输入表，

Declare  @input TABLE(Name VARCHAR(8))
INSERT INTO @input(Name)  values('Aryan') 
INSERT INTO @input(Name)  values('Aryan') 
INSERT INTO @input(Name)  values('Joseph') 
INSERT INTO @input(Name)  values('Vicky') 
INSERT 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') 
INSERT INTO @input(Name)  values('Jaesmin') 
INSERT INTO @input(Name)  values('Vick') 
INSERT INTO @input(Name)  values('Padukon') 
INSERT INTO @input(Name)  values('Joseph') 
INSERT INTO @input(Name)  values('Marya') 
INSERT INTO @input(Name)  values('Vicky')

我也有一个统计表，如

declare @t table(n int)
insert into @t select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11 union all
select 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 中，如果我这样做，因为

 Select rn, name from (
    select ROW_NUMBER()over (order by Name) as rn , * from @input) x
    where rn % 2 <> 0

我得到的输出为

rn  name
1   Aryan
3   Aryan
5   Jaesmin
7   Jaesmin
9   Joseph
11  Padukon
13  Vick
15  Vicky

Bu，我仅限于 Sql server 2000。我怎样才能获得相同的输出？

我已经尝试过

SELECT name, (SELECT COUNT(*) FROM @input AS i2 WHERE i2.Name <= i1.Name) As rn
FROM @input AS i1

，但输出是错误的

name    rn
Aryan   4
Aryan   4
Joseph  9
Vicky   16
Jaesmin 7
Aryan   4
Jaesmin 7
Vicky   16
Padukon 12
Aryan   4
Jaesmin 7
Vick    13
Padukon 12
Joseph  9
Marya   10
Vicky   16

Answer 1

将您的表变量声明为

Declare  @input TABLE(_id int identity(1, 1), Name VARCHAR(8))

然后将您的查询重新编写为

Select _id, name 
from @input
where _id % 2 <> 0

Answer 2

使用此查询：

SELECT t1.name, t.n
FROM
(
    SELECT a.name, a.c, (SELECT COUNT(*) FROM @input AS i2 WHERE i2.Name <= a.Name) [rn]
    FROM
    (
        SELECT i.name, count(*) c
        FROM @input i
        GROUP BY i.name
    )a
)t1
JOIN @t t ON t.n <= t1.rn
WHERE t.n > t1.rn - t1.c 

它会产生所需的输出：

name     n
-------- -----------
Aryan    1
Aryan    2
Aryan    3
Aryan    4
Jaesmin  5
Jaesmin  6
Jaesmin  7
Joseph   8
Joseph   9
Marya    10
Padukon  11
Padukon  12
Vick     13
Vicky    14
Vicky    15
Vicky    16