分页的SQL语句我尝试了半天,不断的问人,在网上找知识点,还有老铁的不断支持,一起研究,他给我说SQL语句已经弄好了,我的SQL语句测试成功,我感觉老铁的比我的要简洁很多。感想:很多时候不是网上没有资料,而是自己搜的姿势不对
刚开始在页面显示多少条SQL语句两种方法:
很多代码,只不过想法不同而已,则代码不同
SELECT * from (select ROW_NUMBER()over(order by AcademeID) as hh,AcademeID,AcademeName,AcademeCode from SYS_Academe)u where hh BETWEEN 1 and 5
//简写:
SELECT * from (select *,ROW_NUMBER()over(order by AcademeID) as hh from SYS_Academe)u where hh BETWEEN 1 and 5
//老铁的方法:
SELECT * from (select *, ROW_NUMBER()over(order by SYS_Academe.AcademeID) as rows from SYS_Academe) i where i.rows BETWEEN 1 and 5
js代码很简单
router.get("/departlist",function (req,res,next) {
var pagenum = req.query.page;
var start;
var end;
if(pagenum == undefined){
pagenum = 1;
start = 0;
end = 5;
}
else{
start = (pagenum -1)*5+1;//第六条开始, 如果这样写:start = (pagenum -1)*5-1 第二页会显示六条数据,而且从第五条开始
end = pagenum*5;
}
sql.connect(db).then(function () {
//sql.query("SELECT * from (select ROW_NUMBER()over(order by AcademeID) as hh,AcademeID,AcademeName,AcademeCode from SYS_Academe)u where hh BETWEEN '"+start+"' and '"+end+"'",function (err,data) {//测试是否可以接受上边声明的条数,是否正确
sql.query("SELECT (select count(*) as RecordCount from SYS_Academe) as count, * FROM (SELECT ROW_NUMBER() OVER (ORDER BY AcademeID) AS ROW_NUMBER, * FROM SYS_Academe AS t0) AS t1 WHERE t1.ROW_NUMBER BETWEEN'"+start+"' and '"+end+"'",function (err,data) {
if (err){
console.log(err);
} else {
res.render("department_list",{departmentList:data.recordset,pagenum:pagenum})
sql.close()
}
})
})
})
页面代码:
<div class="div_page2">
<ul class="change" id="pagelist">
<%if(pagenum == Math.ceil((departmentList[0]["count"]/5))){%>
<li class="pageChange"><a href="" disabled="true">下一页</a></li>
<%}else{%>
<li class="pageChange"><a href="/departlist?page=<%= parseInt(pagenum) + 1%>">下一页</a></li>
<%}%>
<%for(var i = Math.ceil((departmentList[0]["count"]/5)); i >=1 ; i-- ){%>
<%if(i == pagenum){%>
<li class="page current"><a href="/departlist?page=<%= i%>"><%= i %></a></li>
<%}else{%>
<li class="page"><a href="/departlist?page=<%= i%>"><%= i %></a></li>
<%}%>
<%}%>
<%if(pagenum == 1){%>
<li class="pageChange"><a href="" disabled="true">上一页</a></li>
<%}else{%>
<li class="pageChange"><a href="/ ?page=<%= parseInt(pagenum) - 1%>">上一页</a></li>
<%}%>
</ul>
</div>
教程就是那么简单粗暴
表格绑定链接:https://blog.csdn.net/wwq0813/article/details/87384588