SQL之通用查询分页存储过程

-- 创建测试表
if object_id( ' person ') is not null
drop table person

create table person
(
id int primary key identity( 1, 1),
username varchar( 50),
age int,
remark varchar( 100)
)

-- 插入测试数据
declare @i int
set @i = 0
while @i < 100
begin
insert into person values( ' zdw ' + convert( varchar( 5), @i), @i, convert( varchar( 5), @i))
set @i = @i + 1
end
go

select * from person

/**/ /*
* 分页存储过程
* 功能描述：对指定表中满足条件的记录按指定列进行分页查询，分页可以顺序、倒序
* 查询可以指定页大小、指定查询任意页、指定输出字段列表
*/
CREATE PROCEDURE sp_page
@tb varchar( 50), -- 表名
@col varchar( 50), -- 按该列来进行分页
@coltype int, -- @col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby bit, -- 排序,0-顺序,1-倒序
@collist varchar( 800), -- 要查询出的字段列表,*表示全部字段
@selecttype int, -- 查询类型,1-前页,2-后页,3-首页,4-末页,5-指定页
@pagesize int, -- 每页记录数
@page int, -- 指定页
@minid varchar( 50), -- 当前页最小号
@maxid varchar( 50), -- 当前页最大号
@condition varchar( 800) -- 查询条件
AS

DECLARE @sql nvarchar( 4000), @where1 varchar( 800), @where2 varchar( 800)
DECLARE @i int, @id varchar( 50)
IF @coltype = 1 or @coltype = 2 -- 字段类型为字符或日期时间要加上引号以作比较用
BEGIN
SET @minid = '''' + @minid + ''''
SET @maxid = '''' + @maxid + ''''
END
IF @condition is null or rtrim( @condition) = '' -- 没有查询条件
BEGIN
SET @where1 = ' WHERE '
SET @where2 = ' '
END
ELSE -- 有查询条件
BEGIN
SET @where1 = ' WHERE ( ' + @condition + ' ) AND ' -- 本来有条件再加上此条件
SET @where2 = ' WHERE ( ' + @condition + ' ) ' -- 原本没有条件而加上此条件
END
SET @sql =
CASE @selecttype
WHEN 1 -- 前页
THEN ' SELECT * FROM (SELECT TOP ' + CAST( @pagesize AS varchar) +
' ' + @collist + ' FROM ' + @tb + @where1 + @col +
CASE @orderby WHEN 0 THEN ' < ' + @minid ELSE ' > ' + @maxid END +
' ORDER BY ' + @col + CASE @orderby WHEN 0 THEN ' DESC ' ELSE '' END +
' ) t ORDER BY ' + @col + CASE @orderby WHEN 0 THEN '' ELSE ' DESC ' END
WHEN 2 -- 后页
THEN ' SELECT TOP ' + CAST( @pagesize AS varchar) + ' ' + @collist +
' FROM ' + @tb + @where1 + @col +
CASE @orderby WHEN 0 THEN ' > ' + @maxid ELSE ' < ' + @minid END +
' ORDER BY ' + @col + CASE @orderby WHEN 0 THEN '' ELSE ' DESC ' END
WHEN 3 -- 首页
THEN ' SELECT TOP ' + CAST( @pagesize AS varchar) + ' ' + @collist +
' FROM ' + @tb + @where2 + ' ORDER BY ' + @col +
CASE @orderby WHEN 0 THEN '' ELSE ' DESC ' END
WHEN 4 -- 末页
THEN ' SELECT * FROM (SELECT TOP ' + CAST( @pagesize AS varchar) + ' ' +
@collist + ' FROM ' + @tb + @where2 + ' ORDER BY ' + @col +
CASE @orderby WHEN 0 THEN ' DESC ' ELSE '' END + ' ) t ORDER BY ' +
@col + CASE @orderby WHEN 0 THEN '' ELSE ' DESC ' END
END
IF @selecttype >= 1 and @selecttype <= 4
BEGIN
EXEC( @sql)
RETURN
END
ELSE
BEGIN -- 指定页
IF @coltype = 1
IF @orderby = 0
SET @id = ''''''
ELSE
SET @id = '''' + CHAR( 255) + ''''
ELSE
IF @coltype = 2
IF @orderby = 0
SET @id = ''' 1753-1-1 '''
ELSE
SET @id = ''' 9999-12-31 '''
ELSE
IF @orderby = 0
SET @id = ' -2147483648 '
ELSE
SET @id = ' 2147483647 '
SET @i = 0
-- 为减少之后SELECT TOP 的数据量,此处每10000条循环一次,以尽可能接近所查询页
WHILE @i < @pagesize * @page
BEGIN
IF @i + 10000 < @pagesize * @page
BEGIN
IF @orderby = 0
SET @sql = ' SELECT @id=CASE ' + CAST( @coltype AS varchar) +
' WHEN 1 THEN '''''''' +CAST(MAX( ' + @col + ' ) AS varchar(50))+ ' +
'''''''''' +
' WHEN 2 THEN '''''''' +CONVERT(char(23),MAX( ' + @col + ' ),121)+ ' +
'''''''''' +
' ELSE CAST(MAX( ' + @col + ' ) AS varchar) END FROM (SELECT TOP 10000 ' +
@col + ' FROM ' + @tb + @where1 + @col + ' > ' + @id + ' ORDER BY ' + @col + ' ) t '
ELSE
SET @sql = ' SELECT @id=CASE ' + CAST( @coltype AS varchar) +
' WHEN 1 THEN '''''''' +CAST(MIN( ' + @col + ' ) AS varchar(50))+ ' +
'''''''''' +
' WHEN 2 THEN '''''''' +CONVERT(char(23),MIN( ' + @col + ' ),121)+ ' +
'''''''''' +
' ELSE CAST(MIN( ' + @col + ' ) AS varchar) END FROM (SELECT TOP 10000 ' +
@col + ' FROM ' + @tb + @where1 + @col + ' < ' + @id + ' ORDER BY ' + @col + ' DESC) t '
EXEC sp_executesql @sql,N ' @id varchar(50) OUTPUT ', @id OUTPUT
SET @i = @i + 10000
IF @i + 10000 >= @pagesize * @page
BREAK
END
ELSE
BREAK
END
-- 上面的循环保证下面的子查询最多只有10000条数据
IF @orderby = 0
SET @sql = ' SELECT TOP ' + CAST( @pagesize AS varchar) + ' ' + @collist +
' FROM ' + @tb + @where1 + @col + ' > ' + @id + ' AND ' + @col + ' NOT IN ' +
' (SELECT TOP ' + CAST( @pagesize *( @page - 1) - @i AS varchar) +
' ' + @col + ' FROM ' + @tb + @where1 + @col + ' > ' + @id + ' ORDER BY ' + @col +
' ) ORDER BY ' + @col
ELSE
SET @sql = ' SELECT TOP ' + CAST( @pagesize AS varchar) + ' ' + @collist +
' FROM ' + @tb + @where1 + @col + ' < ' + @id + ' AND ' + @col + ' NOT IN ' +
' (SELECT TOP ' + CAST( @pagesize *( @page - 1) - @i AS varchar) +
' ' + @col + ' FROM ' + @tb + @where1 + @col + ' < ' + @id + ' ORDER BY ' + @col +
' DESC) ORDER BY ' + @col + ' DESC '
EXEC( @sql)
END
GO

-- 测试
exec sp_page ' person ', ' id ', 0, 0, ' * ', 5, 10, 8, '', '', ' id != 77 '

对于比较简单的分页需求，一句sql搞定。

-- 第一个10表示pagesize,第二个10用pagesize*(pagenum-1)来算
-- 比如取第2页10 * (2-1) ,第3页 10 * (3 -1)
select top 10 * from person where id not in ( select top 10 id from person)

大家还有什么好的分页sql都拿出来分享下吧