sql 分页[转]
declare @tables VARCHAR(2000),--支持多表名，表1 a,表2 b where a.编码=b.编码
@fields VARCHAR(2000), --显示列名，如果是全部字段则为*
@pkfield VARCHAR(100), --单一主键或唯一值键,支持,隔开组合字段
@strwhere VARCHAR(2000), --查询条件 不含'where'字符，如id>10 and LEN(userid)>9
@orderfield VARCHAR(500), --排序技持多字段排序,不含'order by'字符，如id asc,userid desc
@pagesize INT, --每页输出的记录数
@pageindex INT, --当前页数
@strgroup VARCHAR(500), --汇总字段
@rowcount INT --记录总数 0:会返回总记录
SET @tables='product a'
SET @fields='a.*'
SET @pkfield='a.id'
SET @strwhere=''
SET @orderfield='BigClassName,SmallClassName'
SET @pagesize=20
SET @pageindex=1
SET @strgroup=''
exec get_page @tables,@fields,@pkfield,@strwhere,@orderfield,@pagesize,@pageindex,@strgroup,@rowcount output
select @rowcount
use OA
go
alter proc Get_page
@tables VARCHAR(2000),--支持多表名，表1 a,表2 b where a.编码=b.编码
@fields VARCHAR(2000), --显示列名，如果是全部字段则为*
@pkfield VARCHAR(100), --单一主键或唯一值键,支持,隔开组合字段
@strwhere VARCHAR(2000), --查询条件 不含'where'字符，如id>10 and LEN(userid)>9
@orderfield VARCHAR(500), --排序技持多字段排序,不含'order by'字符，如id asc,userid desc
@pagesize INT, --每页输出的记录数
@pageindex INT, --当前页数
@strgroup VARCHAR(500), --汇总字段
@rowcount INT output --记录总数 0:会返回总记录
as
begin
SET NOCOUNT ON
IF ISNULL(@rowcount,'') = '' OR @rowcount < 0 SET @rowcount = 0
SET @orderfield = RTRIM(LTRIM(@orderfield))
SET @pkfield = RTRIM(LTRIM(@pkfield))
IF @pagesize < 0 SET @pagesize=20
IF ISNULL(@pageindex,0) < 0 SET @pageindex = 1
WHILE CHARINDEX(', ',@orderfield) > 0 OR CHARINDEX(' ,',@orderfield) > 0
BEGIN
SET @orderfield = REPLACE(@orderfield,', ',',')
SET @orderfield = REPLACE(@orderfield,' ,',',')
END
IF ISNULL(@tables,'') = '' OR ISNULL(@fields,'') = '' OR ISNULL(@pkfield,'') = '' RETURN
DECLARE @new_where1 VARCHAR(1000),@new_where2 VARCHAR(1000), @new_order1 VARCHAR(1000),@new_order2 VARCHAR(1000), @strsql NVARCHAR(4000),@pagecount int,@sort_type INT --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
DECLARE @i_index INT,@i_length INT,@strfield_r VARCHAR(100),@strtemp VARCHAR(500),@strorder_r1 VARCHAR(500),@strorder_r2 VARCHAR(500),@strorder_r3 VARCHAR(500),@strpkfield_r VARCHAR(100)
--设置排序顺序及排序字段--
IF ISNULL(@orderfield,'') = '' AND CHARINDEX(',',@pkfield)=0
SET @sort_type='1'
ELSE IF CHARINDEX(',',@orderfield)>0 --多列排序，必须包含主键，且放置最后，否则不处理
BEGIN
SET @sort_type='3'
IF CHARINDEX('ASC', @orderfield)=0 OR CHARINDEX('DESC', @orderfield)=0
BEGIN
SET @strtemp = @orderfield + ','
SET @orderfield = ''
SET @i_index = CHARINDEX(',',@strtemp)
WHILE @i_index > 0
BEGIN
SET @strfield_r = LEFT(@strtemp, @i_index-1)
SET @orderfield = @orderfield + CASE WHEN @orderfield='' then '' else ',' end + @strfield_r + ' ASC'
SET @strtemp = RIGHT(@strtemp, len(@strtemp)-@i_index)
SET @i_index = CHARINDEX(',',@strtemp)
END
END
IF CHARINDEX(',',@pkfield) = 0
BEGIN
IF CHARINDEX(@pkfield, @orderfield) = 0 SET @orderfield = @orderfield + ','+@pkfield+' ASC'
END
ELSE
BEGIN
SET @strtemp = @pkfield + ','
SET @i_index = CHARINDEX(',',@strtemp)
WHILE @i_index>0
BEGIN
SET @strfield_r = LEFT(@strtemp, @i_index-1)
IF CHARINDEX(@strfield_r, @orderfield) = 0 SET @orderfield = @orderfield + ',' + @strfield_r + ' ASC'
SET @strtemp = RIGHT(@strtemp, len(@strtemp)-@i_index)
SET @i_index = CHARINDEX(',',@strtemp)
END
END
END
ELSE --单列排序,需要区分是否存在主键字段
BEGIN
IF CHARINDEX(',',@pkfield)>0 --主键中存在多字段
BEGIN
SET @sort_type='3'
IF LTRIM(@orderfield)<>'' AND (CHARINDEX('ASC', @orderfield)=0 OR CHARINDEX('DESC', @orderfield)=0) SET @orderfield = @orderfield + ' ASC'
SET @strtemp = @pkfield + ','
SET @i_index = CHARINDEX(',',@strtemp)
WHILE @i_index>0
BEGIN
SET @strfield_r = LEFT(@strtemp, @i_index-1)
IF CHARINDEX(@strfield_r, @orderfield) = 0 SET @orderfield = @orderfield + CASE WHEN @orderfield='' then '' else ',' end + @strfield_r + ' ASC'
SET @strtemp = RIGHT(@strtemp, len(@strtemp)-@i_index)
SET @i_index = CHARINDEX(',',@strtemp)
END
END
ELSE --字键中只存在一个字段
BEGIN
SELECT @sort_type=CASE WHEN CHARINDEX('DESC',@orderfield)>0 THEN '2' ELSE '1' END
IF LTRIM(@orderfield)<>''
BEGIN
IF CHARINDEX(@pkfield, @orderfield) = 0
BEGIN
SET @sort_type='3'
SELECT @orderfield = @orderfield + CASE WHEN CHARINDEX('DESC',@orderfield)>0 OR CHARINDEX('ASC',@orderfield)>0 THEN '' ELSE ' ASC' END + ',' + @pkfield + ' ASC'
END
END
END
END
--设置排序顺序及排序字段--
IF(@pkfield<>'' and @pageindex>1)
BEGIN
SET @strtemp = @pkfield
SET @i_index = CHARINDEX('.',@strtemp)
WHILE @i_index>0
BEGIN
SET @strfield_r = LEFT(@strtemp, @i_index)
SET @strpkfield_r = REPLACE(@pkfield,@strfield_r,'TMP.')
SET @strtemp = RIGHT(@strtemp, CASE WHEN CHARINDEX(',',@strtemp)>0 then LEN(@strtemp)-CHARINDEX(',',@strtemp) ELSE 0 END)
SET @i_index = CHARINDEX('.',@strtemp)
END
END
IF ISNULL(@strwhere,'') = ''
BEGIN
SELECT @new_where1 = ' '
SELECT @new_where2 = CASE WHEN CHARINDEX('where',@tables)>0 THEN ' AND ' ELSE ' WHERE ' END + ' '
END
ELSE
BEGIN
SELECT @new_where1 = CASE WHEN CHARINDEX('where',@tables)>0 then ' AND ' ELSE ' WHERE ' END + @strwhere
SELECT @new_where2 = CASE WHEN CHARINDEX('where',@tables)>0 then ' AND ' ELSE ' WHERE ' END + @strwhere + ' AND '
END
IF ISNULL(@orderfield,'') = '' or @sort_type = 1 OR @sort_type = 2
BEGIN
IF @sort_type = 1
BEGIN
SET @new_order1 = ' ORDER BY ' + @pkfield + ' ASC'
SET @new_order2 = ' ORDER BY ' + @pkfield + ' DESC'
SET @strorder_r1 = ' ORDER BY ' + @strpkfield_r + ' ASC'
SET @strorder_r2 = ' ORDER BY ' + @strpkfield_r + ' DESC'
END
IF @sort_type = 2
BEGIN
SET @new_order1 = ' ORDER BY ' + @pkfield + ' DESC'
SET @new_order2 = ' ORDER BY ' + @pkfield + ' ASC'
SET @strorder_r1 = ' ORDER BY ' + @strpkfield_r + ' DESC'
SET @strorder_r2 = ' ORDER BY ' + @strpkfield_r + ' ASC'
END
END
IF @sort_type = 3 --AND CHARINDEX(','+@pkfield+' ',','+@orderfield)>0
BEGIN
SET @new_order1 = @orderfield
SET @new_order2 = @orderfield + ','
SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')
SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
IF(@new_order1<>'' and @pageindex>1)
BEGIN
SET @strtemp = @new_order1
SET @i_index = CHARINDEX('.',@strtemp)
WHILE @i_index>0
BEGIN
SET @strfield_r = LEFT(@strtemp, @i_index)
SET @strorder_r1 = REPLACE(@new_order1,@strfield_r,'TMP.')
SET @strtemp = RIGHT(@strtemp, CASE WHEN CHARINDEX(',',@strtemp)>0 THEN LEN(@strtemp)-CHARINDEX(',',@strtemp) ELSE 0 END)
SET @i_index = CHARINDEX('.',@strtemp)
END
END
IF(@new_order2<>'' and @pageindex>1)
BEGIN
SET @strtemp = @new_order2
SET @i_index = CHARINDEX('.',@strtemp)
WHILE @i_index>0
BEGIN
SET @strfield_r = LEFT(@strtemp, @i_index)
SET @strorder_r2 = REPLACE(@new_order2,@strfield_r,'TMP.')
SET @strtemp = RIGHT(@strtemp, CASE WHEN CHARINDEX(',',@strtemp)>0 THEN LEN(@strtemp)-CHARINDEX(',',@strtemp) ELSE 0 END)
SET @i_index = CHARINDEX('.',@strtemp)
END
SET @strorder_r2=LEFT(@strorder_r2,LEN(@strorder_r2)-1)
END
SET @new_order1 = ' ORDER BY ' + @orderfield
SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
SET @strorder_r1 = ' ORDER BY ' + @strorder_r1
SET @strorder_r2 = ' ORDER BY ' + @strorder_r2
END
IF @rowcount = 0
BEGIN
SET @strsql = 'select @rowcount=count(1) from (select 1 val from ' + @tables + @new_where1 + @strgroup+' ) tab_sel'
EXEC SP_EXECUTESQL @strsql,N'@rowcount INT OUTPUT',@rowcount OUTPUT
END
set @pagecount = CEILING((@rowcount+0.0)/@pagesize)
IF @pageindex > @pagecount SET @pageindex = @pagecount
IF @pageindex = 1 OR @pageindex >= @pagecount
BEGIN
IF @pageindex = 1 --返回第一页数据
SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' +CONVERT(VARCHAR(10),@rowcount)+' as row_count,'+ @fields + ' FROM '+ @tables + @new_where1 + @strgroup + @new_order1
ELSE --返回最后一页数据
SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' + CONVERT(VARCHAR(10),@rowcount)+' as row_count,' + ' * FROM ('+ 'SELECT TOP ' + CONVERT(VARCHAR(10),ABS(@pagesize*@pageindex-@rowcount-@pagesize))+ ' ' + @fields + ' FROM '+ @tables + @new_where1 + @strgroup + @new_order2 + ' ) AS TMP '+ @strorder_r1
END
ELSE
BEGIN
IF @sort_type = 1 --仅主键正序排序
BEGIN
IF @pageindex <= @pagecount/2 --正向检索
BEGIN
SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' +CONVERT(VARCHAR(10),@rowcount)+' as row_count,' + @fields + ' FROM '+ @tables + @new_where2 + @pkfield + ' > ' + '(SELECT MAX(TBPkField) FROM (SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize*(@pageindex-1)) + ' ' + @pkfield + ' as TBPkField FROM ' + @tables + @new_where1 + @strgroup + @new_order1 +' ) AS TMP) ' + @strgroup + @new_order1
END
ELSE --反向检索
BEGIN
SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize)+ ' ' +CONVERT(VARCHAR(10),@rowcount)+' as row_count,' + ' * FROM (' + 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' + @fields + ' FROM ' + @tables + @new_where2 + @pkfield + ' < ' + '(SELECT MIN(TBPkField) FROM (SELECT TOP '
+ CONVERT(VARCHAR(10),@rowcount-@pagesize*@pageindex) + ' ' + @pkfield + ' as TBPkField FROM ' + @tables + @new_where1 + @strgroup + @new_order2 +' ) AS TMP) ' + @strgroup + @new_order2 + ' ) AS TMP ' + @strorder_r1
END
END
ELSE IF @sort_type = 2 --仅主键反序排序
BEGIN
IF @pageindex <= @pagecount/2 --正向检索
BEGIN
SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' '+CONVERT(VARCHAR(10),@rowcount)+' as row_count,' + @fields + ' FROM ' + @tables + @new_where2 + @pkfield + ' < ' + '(SELECT MIN(TBPkField) FROM (SELECT TOP '
+ CONVERT(VARCHAR(10),@pagesize*(@pageindex-1)) + ' ' + @pkfield +' as TBPkField FROM '+ @tables + @new_where1 + @strgroup + @new_order1 + ') AS TMP) '+ @strgroup + @new_order1
END
ELSE --反向检索
BEGIN
SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' +CONVERT(VARCHAR(10),@rowcount)+' as row_count,'+ ' * FROM (' + 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' + @fields + ' FROM ' + @tables + @new_where2 + @pkfield + ' > ' + '(SELECT MAX(TBPkField) FROM (SELECT TOP ' + CONVERT(VARCHAR(10),@rowcount-@pagesize*@pageindex) + ' ' + @pkfield
+ ' as TBPkField FROM ' + @tables + @new_where1 + @strgroup + @new_order2 +' ) AS TMP) ' + @strgroup + @new_order2 + ' ) AS TMP ' + @strorder_r1
END
END
ELSE IF @sort_type = 3 --多列排序
BEGIN
IF @pageindex <= @pagecount/2 --正向检索
BEGIN
SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' '+CONVERT(VARCHAR(10),@rowcount)+' as row_count,'+ ' * FROM ( ' + 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' * FROM ( ' + ' SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize*@pageindex) + ' ' + @fields
+ ' FROM ' + @tables + @new_where1 + @strgroup + @new_order1 + ' ) AS TMP ' + @strorder_r2 + ' ) AS TMP ' + @strorder_r1
END
ELSE --反向检索
BEGIN
SET @strsql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' ' +CONVERT(VARCHAR(10),@rowcount)+' as row_count,'+ ' * FROM ( ' + 'SELECT TOP ' + CONVERT(VARCHAR(10),@pagesize) + ' * FROM ( '
+ ' SELECT TOP ' + CONVERT(VARCHAR(10),@rowcount-@pagesize *@pageindex+@pagesize) + ' ' + @fields + ' FROM ' + @tables + @new_where1 + @strgroup + @new_order2 + ' ) AS TMP ' + @strorder_r1 + ' ) AS TMP ' + @strorder_r1
END
END
END
EXEC(@strsql)
end
