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 [email protected]) order by Score desc) as T1
union all
select * from (select top 4 * from T where Score> (select Score from T where [email protected]) 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 <[email protected] ORDER BY ID DESC) A
union all
SELECT * FROM (select top 4 * from T where ID> @i ORDER BY ID) B
ORDER BY ID