得到用户积分(积分有重复的)排名：共取10个用户的积分排名列表，要求指定Id的用户排在中间(第6位)
T表，ID表示用户id号，Score表示用户积分

共取10个用户的积分排名列表，要求所指定Id的用户排在中间(第6位)

if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N '[dbo].[T] ')   and   OBJECTPROPERTY(id,   N 'IsUserTable ')   =   1)
drop   table   [dbo].[T]
GO

create   table   T(ID   int   ,Score   int)
insert   T(ID,Score)   select   1,100
union   all   select   2,101
union   all   select   3,101
union   all   select   4,102
union   all   select   5,103
union   all   select   6,105
union   all   select   7,105
union   all   select   8,106
union   all   select   9,107
union   all   select   10,108
union   all   select   11,109
union   all   select   12,110
union   all   select   13,116
union   all   select   14,117
union   all   select   15,118
union   all   select   16,101
union   all   select   17,103

按上表数据，我指定ID=9，想得到下面的列表
ID         Score
------------
5           103
17         103
6           105
7           105
8           106
9           107
10         108
11         109
12         110
13         116

------解决方案--------------------
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[T] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [dbo].[T]
GO

create table T(ID int ,Score int)
insert T(ID,Score) select 1,100
union all select 2,101
union all select 3,101
union all select 4,102
union all select 5,103
union all select 6,105
union all select 7,105
union all select 8,106
union all select 9,107
union all select 10,108
union all select 11,109
union all select 12,110
union all select 13,116
union all select 14,117
union all select 15,118
union all select 16,101
union all select 17,103


declare @i int

set @i=9

select * from (select top 6 * from T where Score <=(select Score from T where id =@i) order by Score desc) as T1
union all
select * from (select top 4 * from T where Score> (select Score from T where id=@i) order by Score ) as T2
order by score,id
------解决方案--------------------

DECLARE @i INT
SET @i=9
SELECT * FROM (SELECT TOP 6 * FROM T a WHERE ID <=@i ORDER BY ID DESC) A
union all
SELECT * FROM (select top 4 * from T where ID> @i ORDER BY ID) B
ORDER BY ID