如何在分页上连续获取Mysql rownum [英] How to get Mysql rownum continously over the pagenation
问题描述
基本上,我想通过使用@rownum显示表中的行数以及数据,它对第一页来说效果很好,但是当我们转到下一页时,它又从第一行开始.
Basically, i want to display the number of rows from table along with the data by using @rownum it works just fine for first page but when we go to the next page we it start with row one again.
查询代码:
$sql = "SELECT @rownum:=@rownum+1 as rownum, t.*FROM (SELECT @rownum:=0) r, (select * from tbl) t
LIMIT $Page_Start , $Per_Page";
分页代码:
$objConnect = mysql_connect("localhost","user","pass") or die("Error Connect to Database");
`enter code here`$objDB = mysql_select_db("WEB");
$strSQL = "SELECT * FROM line ";
$objQuery = mysql_query($strSQL) or die ("Error Query [".$strSQL."]");
$Num_Rows = mysql_num_rows($objQuery);
$Per_Page = 2; // Per Page
$Page = mysql_real_escape_string($_GET["Page"]);
if(!$_GET["Page"])
{
$Page=1;
}
$Prev_Page = $Page-1;
$Next_Page = $Page+1;
$Page_Start = (($Per_Page*$Page)-$Per_Page);
if($Num_Rows<=$Per_Page)
{
$Num_Pages =1;
}
else if(($Num_Rows % $Per_Page)==0)
{
$Num_Pages =($Num_Rows/$Per_Page) ;
}
else
{
$Num_Pages =($Num_Rows/$Per_Page)+1;
$Num_Pages = (int)$Num_Pages;
}
分页使用情况:
if($Prev_Page)
{
echo "<a href='$_SERVER[SCRIPT_NAME]?Page=$Prev_Page'> Back</a>   ";
}
if($Page!=$Num_Pages)
{
echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$Next_Page'>Next</a> ";
}
所以我希望行数不断增加 示例第1页的第1-5行和第2页的应为6-10之类的
So i want the number of row continuously increase page after pages example page 1 row 1-5 and page2 should be 6-10 something like that
非常感谢
推荐答案
您需要将计算结果包装起来以在子查询中获取行号,并将其限制在外部的SELECT
语句中,以便row_number不会中断,例如
You need to wrap the calculation to get row number inside a subquery and limit it on the outer SELECT
statement so the row_number won't break, ex,
列名和表名可能与上面的示例不同,但查询的思想是相同的.
SELECT RowNumber, Student_ID, Student_Name
FROM
(
SELECT @rownum := @rownum + 1 RowNumber,
t.*
FROM student t, (SELECT @rownum := 0) s
ORDER BY t.Student_ID
) subQ
// LIMIT 0, 3
- SQLFiddle演示(有LIMIT,但即使在分页中也可以继续)
这篇关于如何在分页上连续获取Mysql rownum的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!