关于java 分页取出数据所在页的页数。

给你提供两种思路:

1你要取第二页，也就就是6-10，你可以先用top 10 ，取出前10个，然后desc倒序，再取出top 5

再倒序，这样就取出6-10个；

比如：select top 5 from (select top 10 from table_1 desc) t desc

2sqlserver 和oracle都有row_number() 这样的列，就是你取出的结果集给你多加一个列，你按row_number()去结果集也行。

比如：with table001 as (select row_number() over (order by Qid) as row_number , from ( "+sql+") k) select from table001 where row_number>= and row_number<= order by Qid

这个是我之前项目中的sql语句。你参考一下，作用就是 取( "+sql+") 中的sql 中的两个？之间的数据

---------------------------

你要取页数，可以用第二个思路，pk不一定是连续，可能有些数据给删除；

但是查询出来的结果集加上的row_number()，肯定是连续，

row_number%5==0；page = row_number/5

row_number%5!=0；page = row_number/5+1

1、首先获取总数据条数 select count(id) from table

2、然后根据总数获取总页数

3、根据ajax传递参数，page,size

sql语句如下

select from table limit (page-1) size , size

描述较为简略，如有疑问可追答

我们一个留言版或者文章系统等系统的时候,往往会出现太多的文章, 以致网页的美感大大形响了那有何办法呢下向我向大家写下一个分页教程,写得不好,望大家不要笑我毕竟本人也是第一次(会懂分页程序的高手建议看看便好,不要学习以免打乱你以往的编程习惯)^_^

程序例子: >

SQLSERVER分页：

CREATE PROCEDURE UP_GetDataList

@tblName varchar(255), -- 表名

@strGetFields varchar(1000) = '', -- 需要返回的列

@fldName varchar(255)='', -- 排序的字段名

@PageSize int = 10, -- 页尺寸

@PageIndex int = 1, -- 页码

@doCount bit = 0, -- 返回记录总数, 非 0 值则返回

@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序

@strWhere nvarchar(1000) = '' -- 查询条件 (注意: 不要加 where)

AS

declare @strSQL nvarchar(4000) -- 主语句

declare @strTmp varchar(110) -- 临时变量

declare @strOrder varchar(400) -- 排序类型

if @doCount != 0 --如果@doCount传递过来的不是0，就执行总数统计

begin

if @strWhere !=''

set @strSQL = 'SELECT COUNT() AS Total FROM ' + @tblName + ' WHERE ' + @strWhere

else

set @strSQL = 'SELECT COUNT() AS Total FROM ' + @tblName

end

else

begin

if @OrderType != 0

begin

set @strTmp = '<(SELECT MIN'

set @strOrder = ' ORDER BY [' + @fldName +'] DESC'

--如果@OrderType不是0，就执行降序

end

else

begin

set @strTmp = '>(SELECT MAX'

set @strOrder = ' ORDER BY [' + @fldName +'] ASC'

end

if @PageIndex = 1

begin

if @strWhere != ''

set @strSQL = 'SELECT TOP ' + str(@PageSize) +' '+@strGetFields+ ' FROM [' + @tblName + '] WHERE ' + @strWhere + ' ' + @strOrder

else

set @strSQL = 'SELECT TOP ' + str(@PageSize) +' '+@strGetFields+ ' FROM ['+ @tblName + '] '+ @strOrder

--如果是第一页就执行以上代码，这样会加快执行速度

end

else

begin

--以下代码赋予了@strSQL以真正执行的SQL代码

set @strSQL = 'SELECT TOP ' + str(@PageSize) +' '+@strGetFields+ ' FROM ['

+ @tblName + '] WHERE [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) FROM (SELECT TOP ' + str((@PageIndex-1)@PageSize) + ' ['+ @fldName + '] FROM [' + @tblName + ']' + @strOrder + ') AS tblTmp)'+ @strOrder

if @strWhere != ''

set @strSQL = 'SELECT TOP ' + str(@PageSize) +' '+@strGetFields+ ' FROM ['

+ @tblName + '] WHERE [' + @fldName + ']' + @strTmp + '(['

+ @fldName + ']) FROM (SELECT TOP ' + str((@PageIndex-1)@PageSize) + ' ['

+ @fldName + '] FROM [' + @tblName + '] WHERE ' + @strWhere + ' '

+ @strOrder + ') AS tblTmp) AND ' + @strWhere + ' ' + @strOrder

end

end

exec (@strSQL)

GO

----------------------

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author: DavidYan

-- Create date: 20071217

-- Description:

-- =============================================

Create PROC P_viewPage

-- Add the parameters for the stored procedure here

@TableName VARCHAR(200), --表名

@FieldList VARCHAR(2000), --显示列名，如果是全部字段则为

@PrimaryKey VARCHAR(100), --单一主键或唯一值键

@Where VARCHAR(2000), --查询条件 不含'where'字符，如id>10 and len(userid)>9

@Order VARCHAR(1000), --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc

--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷

@SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法

@RecorderCount INT, --记录总数 0:会返回总记录

@PageSize INT, --每页输出的记录数

@PageIndex INT, --当前页数

@TotalCount INT OUTPUT, --记返回总记录

@TotalPageCount INT OUTPUT --返回总页数

AS

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements

SET NOCOUNT ON

IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0

SET @Order = RTRIM(LTRIM(@Order))

SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))

SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')

WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0

BEGIN

SET @Order = REPLACE(@Order,', ',',')

SET @Order = REPLACE(@Order,' ,',',')

END

IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = ''

OR ISNULL(@PrimaryKey,'') = ''

OR @SortType < 1 OR @SortType >3

OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0

BEGIN

PRINT('ERR_00参数错误')

RETURN

END

IF @SortType = 3

BEGIN

IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')

BEGIN

PRINT('ERR_02排序错误') RETURN END

END

DECLARE @new_where1 VARCHAR(1000)

DECLARE @new_where2 VARCHAR(1000)

DECLARE @new_order1 VARCHAR(1000)

DECLARE @new_order2 VARCHAR(1000)

DECLARE @new_order3 VARCHAR(1000)

DECLARE @Sql VARCHAR(8000)

DECLARE @SqlCount NVARCHAR(4000)

IF ISNULL(@where,'') = ''

BEGIN

SET @new_where1 = ' '

SET @new_where2 = ' WHERE '

END

ELSE

BEGIN

SET @new_where1 = ' WHERE ' + @where

SET @new_where2 = ' WHERE ' + @where + ' AND '

END

IF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2

BEGIN

IF @SortType = 1

BEGIN

SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC'

SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC'

END

IF @SortType = 2

BEGIN

SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC'

SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC'

END

END

ELSE

BEGIN

SET @new_order1 = ' ORDER BY ' + @Order

END

IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0

BEGIN

SET @new_order1 = ' ORDER BY ' + @Order

SET @new_order2 = @Order + ','

SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')

SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')

SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)

IF @FieldList <> ''

BEGIN

SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')

SET @FieldList = ',' + @FieldList

WHILE CHARINDEX(',',@new_order3)>0

BEGIN

IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0

BEGIN

SET @FieldList =

@FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))

END

SET @new_order3 = SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))

END

SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))

END

END

SET @SqlCount = 'SELECT @TotalCount=COUNT(),@TotalPageCount=CEILING((COUNT()+00)/'

+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1

IF @RecorderCount = 0

BEGIN

EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',

@TotalCount OUTPUT,@TotalPageCount OUTPUT

END

ELSE

BEGIN

SELECT @TotalCount = @RecorderCount

END

IF @PageIndex > CEILING((@TotalCount+00)/@PageSize)

BEGIN

SET @PageIndex = CEILING((@TotalCount+00)/@PageSize)

END

IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+00)/@PageSize)

BEGIN

IF @PageIndex = 1 --返回第一页数据

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '

+ @TableName + @new_where1 + @new_order1

END

IF @PageIndex >= CEILING((@TotalCount+00)/@PageSize) --返回最后一页数据

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('

+ 'SELECT TOP ' + STR(ABS(@PageSize@PageIndex-@TotalCount-@PageSize))

+ ' ' + @FieldList + ' FROM '

+ @TableName + @new_where1 + @new_order2 + ' ) AS TMP '

+ @new_order1

END

END

ELSE

BEGIN

IF @SortType = 1 --仅主键正序排序

BEGIN

IF @PageIndex <= CEILING((@TotalCount+00)/@PageSize)/2 --正向检索

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '

+ @TableName + @new_where2 + @PrimaryKey + ' > '

+ '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '

+ STR(@PageSize(@PageIndex-1)) + ' ' + @PrimaryKey

+ ' FROM ' + @TableName

+ @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1

END

ELSE --反向检索

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('

+ 'SELECT TOP ' + STR(@PageSize) + ' '

+ @FieldList + ' FROM '

+ @TableName + @new_where2 + @PrimaryKey + ' < '

+ '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '

+ STR(@TotalCount-@PageSize@PageIndex) + ' ' + @PrimaryKey

+ ' FROM ' + @TableName

+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2

+ ' ) AS TMP ' + @new_order1

END

END

IF @SortType = 2 --仅主键反序排序

BEGIN

IF @PageIndex <= CEILING((@TotalCount+00)/@PageSize)/2 --正向检索

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '

+ @TableName + @new_where2 + @PrimaryKey + ' < '

+ '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '

+ STR(@PageSize(@PageIndex-1)) + ' ' + @PrimaryKey

+' FROM '+ @TableName

+ @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1

END

ELSE --反向检索

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('

+ 'SELECT TOP ' + STR(@PageSize) + ' '

+ @FieldList + ' FROM '

+ @TableName + @new_where2 + @PrimaryKey + ' > '

+ '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '

+ STR(@TotalCount-@PageSize@PageIndex) + ' ' + @PrimaryKey

+ ' FROM ' + @TableName

+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2

+ ' ) AS TMP ' + @new_order1

END

END

IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理

BEGIN

IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0

BEGIN

PRINT('ERR_02') RETURN

END

IF @PageIndex <= CEILING((@TotalCount+00)/@PageSize)/2 --正向检索

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '

+ 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '

+ ' SELECT TOP ' + STR(@PageSize@PageIndex) + ' ' + @FieldList

+ ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '

+ @new_order2 + ' ) AS TMP ' + @new_order1

END

ELSE --反向检索

BEGIN

SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '

+ 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '

+ ' SELECT TOP ' + STR(@TotalCount-@PageSize @PageIndex+@PageSize) + ' ' + @FieldList

+ ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '

+ @new_order1 + ' ) AS TMP ' + @new_order1

END

END

END

EXEC(@Sql)

GO

MYSQL分页：

DELIMITER $$

DROP PROCEDURE IF EXISTS `p_pageList`$$

/ m_pageNo 当前页码;

m_perPageCnt int ,每页显示条数;

m_column,查询的字段字符;

m_table,查询的表名;

m_condition,where条件(不用写where);

m_orderBy,排序条件(不用写order by);

m_totalPageCnt,查找的总条数/

CREATE DEFINER=`root`@`%` PROCEDURE `p_pageList`(

m_pageNo int ,

m_perPageCnt int ,

m_column varchar(1000) ,

m_table varchar(1000) ,

m_condition varchar(1000),

m_orderBy varchar(200) ,

out m_totalPageCnt int

)

BEGIN

SET @pageCnt = 1; -- 总记录数

SET @limitStart = (m_pageNo - 1)m_perPageCnt;

SET @limitEnd = m_perPageCnt;

SET @sqlCnt = CONCAT('select count(1) into @pageCnt from ',m_table); -- 这条语句很关键，用来得到总数值

-- @pageCnt变量为统计信息数

SET @sql = CONCAT('select ',m_column,' from ',m_table);

IF m_condition IS NOT NULL AND m_condition <> '' THEN -- 如果条件不为空

SET @sql = CONCAT(@sql,' where ',m_condition); -- 这条SQL语句为查找条件信息

SET @sqlCnt = CONCAT(@sqlCnt,' where ',m_condition); -- 这条SQL语句为查找条件信息的总数量

END IF;

IF m_orderBy IS NOT NULL AND m_orderBy <> '' THEN -- 如果排序条件不为空

SET @sql = CONCAT(@sql,' order by ',m_orderBy); -- SQL语句追加排序

END IF;

SET @sql = CONCAT(@sql, ' limit ', @limitStart, ',', @limitEnd); -- SQL语句添加分页信息

PREPARE s_cnt from @sqlCnt; -- 预处理SQL语句

EXECUTE s_cnt; -- 执行SQL语句

DEALLOCATE PREPARE s_cnt;

SET m_totalPageCnt = @pageCnt; -- 将值输出给 m_totalPageCnt变量

PREPARE record from @sql;

EXECUTE record;

DEALLOCATE PREPARE record;

END$$

DELIMITER ;

这两个应该都容易看懂，就算看不懂也没关系，只要知道传参数用就可以了。

'来个新分页方法 '无错分页方法 '下面这个函数可以包含在连接页面中供所有页面调用 'html-encode:格式化HTML字符(可能无法正常显示,BAIDU格式化^_^) function lq_code_html(lq_code_html_tmp) if not isnull(lq_code_html_tmp) then lq_code_html_tmp=serverhtmlencode(lq_code_html_tmp) lq_code_html_tmp=replace(lq_code_html_tmp,"\","\") lq_code_html_tmp=replace(lq_code_html_tmp,"'","'") lq_code_html_tmp=replace(lq_code_html_tmp,vbCrlf,"<br>") lq_code_html = lq_code_html_tmp end if end function '获取查询内容关键字由 nr=lq_code_html(Request("nr")) '注意在查询输入表单中将输入nrValue设置为<%=nr%>以方便下次调用 '获取页面 page=lq_code_html(Request("page")) '如果不是数字则初始化为第一页 if not isnumeric(page) then page=1 else page=Clng(page) end if '初始化查询语句[下面fws_upfile为查询表名] sql="Select from [fws_upfile]" '如果查询关键字不为空[下面prd_file为查询字段名] if nr<>"" then sql=sql&" where prd_file like '%"&nr&"%'" end if '设定每页显示数量 PageSetup=8 '查询 Set Rs=ServerCreateObject("ADODBRecordSet") Rsopen sql,conn,1,1 RsPagesize=PageSetup '总页数 TotalPage=RsPagecount TotalCode=RsRecordCount if Page <1 then Page = 1 if Page > TotalPage then Page = TotalPage '跳转到指定页数 if TotalPage>0 then RsabsolutePage=Page Do While Not rseof and i<PageSetup '显示开始 '此处是显示代码 '显示结束 RsMoveNext loop RsClose Set Rs=Nothing '分页部分 '注意所有需要查询数据都要传递 ResponseWrite "共"&TotalCode&"条" ResponseWrite "第"&page&"页/共"&TotalPage&"页" ResponseWrite "<a href='menu=search&page=1&nr="&serverUrlencode(nr)&"' target='_self'>首页</a>" ResponseWrite "<a href='menu=search&page="&page-1&"&nr="&serverUrlencode(nr)&"' target='_self'>上页</a>" ResponseWrite "<a href='menu=search&page="&page&"&nr="&serverUrlencode(nr)&"' target='_self'>本页</a>" ResponseWrite "<a href='menu=search&page="&page+1&"&nr="&serverUrlencode(nr)&"' target='_self'>下页</a>" ResponseWrite "<a href='menu=search&page="&TotalPage&"&nr="&serverUrlencode(nr)&"' target='_self'>末页</a>" ''''''''''' Connclose Set Conn=Nothing

以上就是关于关于java 分页取出数据所在页的页数。全部的内容，包括:关于java 分页取出数据所在页的页数。、如何用sql，实现做翻页、100分请教高手ASP分页显示页码个数等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！