Sql server 2000中的Row_Number模拟 [英] Row_Number simulation in Sql server 2000
本文介绍了Sql server 2000中的Row_Number模拟的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个示例输入表
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 中,如果我这样做
In Sql Server 2005 if I do as
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
但我仅限于 Sql server 2000.我怎样才能得到相同的输出?
Bu I am restricted to Sql server 2000. How can I get the same output?
我试过
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
推荐答案
使用这个查询:
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
这篇关于Sql server 2000中的Row_Number模拟的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文