T-SQL 選擇某一記錄的前後相關記錄

 

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