比方有一商品表commodity
编号 名称 其他列...
1 a
2 b
3 c
4 x
5 t
6 a
7 b
如果给定 编号(id) 为 3 需要取 4条记录 ,那么应该返回 ID:1,2,4,5 四条记录
如果给定 id=2 那么返回 ID: 1,3,4,5
如果给定 id=7 那么返回 ID: 3,4,5,7
代码如下(SQL2000)
--------------------------------
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
ALTER Proc Commodity_QueryRelative @id int, @num int =4 As Declare @SQL nvarchar(2000) Declare @highNum int Declare @LowNum int Declare @username nvarchar(50) Set @SQL='' Set @highNum=0 Set @LowNum='' Set @username=''
Select @Username=Username From [Commodity] Where [Id]=@id Select @highNum=Count(ID) From [Commodity] Where [Id]> @id And Username=@Username Select @LowNum=Count(ID) From [Commodity] Where [Id]< @id And Username=@Username
Declare @margin int Declare @halfNum int Set @halfNum=@num/2 Set @margin =0
If @HighNum < @halfNum And @LowNum <@halfNum Begin Set @SQL='Select Top '+ cast(@halfNum as nvarchar) +' [ID], [Title], [Keys], [ClassID], [ClassIDPath], [ClassName], [ClassNamePath], [UserClassID], [UserClassName],[Img], [ThumImg], [AreaID], [AreaName], [Address], [Linkman], [UpdateTime],[AddTime] From Commodity Where Username='''+ @username + ''' And Id !=' + Cast(@id as nvarchar) End Else Begin Declare @LessNum int Declare @largeNum int Set @LessNum=0 Set @LargeNum=0
If @HighNum >=@halfNum And @LowNum >=@halfNum Begin Set @LessNum=@halfNum Set @largeNum=@halfNum End
If @HighNum >= @halfNum And @LowNum< @halfNum Begin Set @LessNum=@halfNum Set @largeNum=@num-@LowNum End
If @HighNum < @halfNum And @LowNum >= @halfNum Begin Set @LessNum=@num-@HighNum Set @largeNum=@halfNum End
Set @SQL='Select * From ( Select Top '+ cast(@LessNum as nvarchar) +' [ID], [Title], [Keys], [ClassID], [ClassIDPath], [ClassName], [ClassNamePath], [UserClassID], [UserClassName],[Img], [ThumImg], [AreaID], [AreaName], [Address], [Linkman], [UpdateTime],[AddTime] From Commodity Where Username='''+ @username + ''' And Id <' + Cast(@id as nvarchar) +' Order By ID DESC ) as t1 Union Select * From ( Select Top '+ cast(@LargeNum as nvarchar) +' [ID], [Title], [Keys], [ClassID], [ClassIDPath], [ClassName], [ClassNamePath], [UserClassID], [UserClassName],[Img], [ThumImg], [AreaID], [AreaName], [Address], [Linkman], [UpdateTime],[AddTime] From Commodity Where Username='''+ @username + ''' And Id >' + Cast(@id as nvarchar) +' ) as T2 ' End
Exec(@SQL)
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
