http://community.csdn.net/Expert/topic/3587/3587201.xml?temp=9.055728E-02CREATE PROCEDURE sp_page
@tb varchar(20), --表名
@col varchar(20), --按该列来进行分页
@coltype bit, --@col列的类型,0-数字类型,1-字符类型
@collist varchar(800),--要查询出的字段列表
@selecttype int, --查询类型,1-前页,2-后页,3-首页,4-末页,5-指定页
@pagesize int, --每页记录数
@page int, --指定页
@minid varchar(50), --当前最小号
@maxid varchar(50) --当前最大号
AS
DECLARE @sql varchar(8000)
IF @coltype=1
BEGIN
SET @minid=''''+@minid+''''
SET @maxid=''''+@maxid+''''
END
SET @sql=
CASE @selecttype
WHEN 1--前页
THEN 'SELECT '+@collist+' FROM (SELECT TOP '+CAST(@pagesize AS varchar)+
' '+@collist+' FROM '+@tb+' WHERE '+@col+'<'+@minid+
' ORDER BY '+@col+' DESC) t ORDER BY '+@col
WHEN 2--后页
THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+' WHERE '+@col+'>'+@maxid+' ORDER BY '+@col
WHEN 3--首页
THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+
' '+@collist+' FROM '+@tb+' ORDER BY '+@col
WHEN 4--末页
THEN 'SELECT '+@collist+' FROM (SELECT TOP '+CAST(@pagesize AS varchar)+
' '+@collist+' FROM '+@tb+' ORDER BY '+@col+' DESC) t ORDER BY '+@col
WHEN 5--指定页
THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+' WHERE '+@col+' NOT IN'+
'(SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+@col+
' FROM '+@tb+' ORDER BY '+@col+') ORDER BY '+@col
END
EXEC(@sql)
GO
@tb varchar(20), --表名
@col varchar(20), --按该列来进行分页
@coltype bit, --@col列的类型,0-数字类型,1-字符类型
@collist varchar(800),--要查询出的字段列表
@selecttype int, --查询类型,1-前页,2-后页,3-首页,4-末页,5-指定页
@pagesize int, --每页记录数
@page int, --指定页
@minid varchar(50), --当前最小号
@maxid varchar(50) --当前最大号
AS
DECLARE @sql varchar(8000)
IF @coltype=1
BEGIN
SET @minid=''''+@minid+''''
SET @maxid=''''+@maxid+''''
END
SET @sql=
CASE @selecttype
WHEN 1--前页
THEN 'SELECT '+@collist+' FROM (SELECT TOP '+CAST(@pagesize AS varchar)+
' '+@collist+' FROM '+@tb+' WHERE '+@col+'<'+@minid+
' ORDER BY '+@col+' DESC) t ORDER BY '+@col
WHEN 2--后页
THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+' WHERE '+@col+'>'+@maxid+' ORDER BY '+@col
WHEN 3--首页
THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+
' '+@collist+' FROM '+@tb+' ORDER BY '+@col
WHEN 4--末页
THEN 'SELECT '+@collist+' FROM (SELECT TOP '+CAST(@pagesize AS varchar)+
' '+@collist+' FROM '+@tb+' ORDER BY '+@col+' DESC) t ORDER BY '+@col
WHEN 5--指定页
THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+' WHERE '+@col+' NOT IN'+
'(SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+@col+
' FROM '+@tb+' ORDER BY '+@col+') ORDER BY '+@col
END
EXEC(@sql)
GO
--- 建立测试数据
IF OBJECT_ID('Tempdb..#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T(I INT)INSERT INTO #T
SELECT Cast (cast(a.i1 as varchar(4)) + cast(b.i2 as varchar(4)) as int)+1 FROM
(Select 1 as i1 union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9 union select 0) a ,
(Select 1 as i2 union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9 union select 0) b
--- 开始查询
declare @i int
set @i =100 --- @I 是参数,可以自己定义 Select * from #T Where I Between Case When @i in (1,2) Then 0 when @i in (99,100) then 95 else @i - 2 end
and Case When @i in (1,2) Then 5 when @i in (99,100) then 100 else @i + 3 end
比如分页,它可以每页10条,1-10,11-20,
但我的要求是,给出一个参数,比如N,那么反回就是N-2至N+2的记录数。
N,始终是在中间
set @n=8 --值
select * from 表
where ROW_NUMBER() OVER(ORDER BY 1 DESC) between @n-2 and @n+2