求一排名语句！
我有一张1000万条数据的表。格式如下：

CREATE TABLE [userinfo] (
[idx] [bigint] IDENTITY (1, 1) NOT NULL ,
[username] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[password] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[score] [int] NOT NULL ,
[lvl] [int] NOT NULL ,
[money] [int] NOT NULL ,
CONSTRAINT [PK_userinfo] PRIMARY KEY CLUSTERED
(
[idx]
) ON [PRIMARY]
) ON [PRIMARY]
GO

现在需要实时的计算某人的以score高低排序的前10名和后10名。

比如：输入 'mary ' ，立即计算出以score从高到低排序的在mary前10人和后10人的数据
username score
.....前10人.....
mary 100
.....后10人.....


万分感谢各位达人帮忙！！

[解决办法]
Select * From (
Select TOP 10 * From userinfo Where Score > (Select Score From userinfo Where username = 'mary ') Order By Score Desc) A
Union All
Select * From userinfo Where username = 'mary '
Union All
Select * From (
Select TOP 10 * From userinfo Where Score < (Select Score From userinfo Where username = 'mary ') Order By Score) B
[解决办法]
我的作法和 paoluo　一。不在後面的10以降序排列了一下。要是多的看起直些。
Select * from (
Select top 10 [username],[score] from userinfo
where score> ( Select score from userinfo where username= 'mary ' )
order by score desc ) A
union all
Select username, score from userinfo where username= 'mary '
union all
Select * from(
Select top 10 [username],[score] from userinfo
where score < ( Select score from userinfo where username= 'mary ' )
order by score asc )B
order by Score desc
[解决办法]
CREATE TABLE [userinfo] (
[idx] [bigint] IDENTITY (1, 1) NOT NULL ,
[username] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[password] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[score] [int] NOT NULL ,
[lvl] [int] NOT NULL ,
[money] [int] NOT NULL ,
CONSTRAINT [PK_userinfo] PRIMARY KEY CLUSTERED
(
[idx]
) ON [PRIMARY]
) ON [PRIMARY]


--插入测试49
declare @i int
set @i=1
while 50> @i
begin
insert userinfo(username,score,lvl,[money]) values( 'mary '+rtrim(@i),1,2,2)
set @i=@i+1
end

select * from userinfo--查询
update userinfo set username= 'mary ' where idx=30--更新第30条记录为mary


查询结果:
declare @username varchar(50)
set @username= 'mary '
select * from userinfo
where idx between --少了一个关键字
(select idx-10 from userinfo where username=@username)
and
(select idx+10 from userinfo where username=@username)

idx username password score lvl money
-------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- -----------
20 mary20 NULL 1 2 2
21 mary21 NULL 1 2 2

22 mary22 NULL 1 2 2
23 mary23 NULL 1 2 2
24 mary24 NULL 1 2 2
25 mary25 NULL 1 2 2
26 mary26 NULL 1 2 2
27 mary27 NULL 1 2 2
28 mary28 NULL 1 2 2
29 mary29 NULL 1 2 2
30 mary NULL 1 2 2
31 mary31 NULL 1 2 2
32 mary32 NULL 1 2 2
33 mary33 NULL 1 2 2
34 mary34 NULL 1 2 2
35 mary35 NULL 1 2 2
36 mary36 NULL 1 2 2
37 mary37 NULL 1 2 2
38 mary38 NULL 1 2 2
39 mary39 NULL 1 2 2
40 mary40 NULL 1 2 2

(21 行受影响)