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 )
张姿势了~_~！