比方有一商品表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