sql查询前后两位

浏览数：26 / 时间：2015年06月12日

SQL排名的问题，A这个人在数据库里排第十，怎么查询一个他前面两位，后面两位，包括自己的五条数据,各位有啥高招？

DECLARE @table TABLE
    (
      id INT PRIMARY KEY
             IDENTITY(1, 1) ,
      NAME VARCHAR(20)
    )
INSERT  INTO @table( NAME )VALUES  ( ‘AA‘ )
INSERT  INTO @table( NAME )VALUES  ( ‘BB‘ )
INSERT  INTO @table( NAME )VALUES  ( ‘CC‘ )
INSERT  INTO @table( NAME )VALUES  ( ‘DD‘ )
INSERT  INTO @table( NAME )VALUES  ( ‘EE‘ )
INSERT  INTO @table( NAME )VALUES  ( ‘FF‘ )
INSERT  INTO @table( NAME )VALUES  ( ‘GG‘ )
INSERT  INTO @table( NAME )VALUES  ( ‘HH‘ )
INSERT  INTO @table( NAME )VALUES  ( ‘II‘ )
INSERT  INTO @table( NAME )VALUES  ( ‘JJ‘ )

方法： 1 .添加一个列，排序，记录中间需要的 mun 数，之后利用 between and 查询

DECLARE @min INT ,
@max INT

SELECT  @min = v.mun - 2 ,
        @max = v.mun + 2
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY id ASC ) AS mun ,id,NAME
          FROM      @table
        ) v
WHERE   v.NAME = ‘ee‘

SELECT  w.id ,
        w.NAME
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY id ASC ) AS mun ,id,name
          FROM      @table
        ) w
WHERE   w.mun BETWEEN @min AND @max

方法2. 利用链表查询，将一个分成两个集合，利用on 做条件 ON t1.mun BETWEEN t2.mun - 2 AND t2.mun + 2

SELECT  t1.id ,
        t1.NAME
FROM    ( SELECT ROW_NUMBER() OVER ( ORDER BY id ASC ) AS mun ,id ,NAME
          FROM      @table
        ) T1
        RIGHT JOIN 
        ( SELECT ROW_NUMBER() OVER ( ORDER BY id ASC ) AS mun ,id ,NAME
                     FROM   @table
        ) T2 ON t1.mun BETWEEN t2.mun - 2 AND t2.mun + 2
WHERE   T2.NAME = ‘EE‘

方法3. 利用EXISTS

例子

SELECT  *
FROM    @table T1
WHERE   EXISTS 
        ( SELECT *
                 FROM   @table T2
                 WHERE  T2.NAME = ‘EE‘
                        AND T1.id BETWEEN T2.id - 2 AND T2.id + 2 )

完善

SELECT  T1.id ,T1.name
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY id ASC ) AS mun ,id ,NAME
          FROM      @table
        ) T1
WHERE   EXISTS 
        ( SELECT mun ,id ,NAME
                 FROM   ( SELECT    ROW_NUMBER() OVER ( ORDER BY id ASC ) AS mun ,id ,NAME
                          FROM      @table
                        ) T2
                 WHERE  T2.NAME = ‘EE‘
                        AND T1.mun BETWEEN T2.mun - 2 AND T2.mun + 2 
        )