SqlServer 实现分页查询
sql = "select * from(select *,(ROW_NUMBER() over(order by MB001 asc))as newId from INVMB) as t where newId between (sPage - 1) * pageCount + 1 and sPage * pageCount
去重 分页 具体代码
// 加载文件受领/回收/被回收记录
app.post('/loadFilesRecord', async function (req, res) {
let obj = req.body
sql = `
with temp as (
select 文件编号,操作时间,状态 from 文件动向表 Where 工号 = '${obj.uNo}' And 文件动向表.备注 = '受领' And 文件动向表.状态 <> ''
)
Select * FROM (
Select ROW_NUMBER() OVER( Order By C.操作时间 Desc) newId, C.* FROM(
Select B.文件编号,文件名称,客户,操作时间,B.状态 From (Select * From (Select ROW_NUMBER() OVER(PARTITION BY temp.文件编号 ORDER BY temp.操作时间 Desc ) rownum, temp.* From temp ) A Where rownum = 1 ) B Left Join 文件记录表 On B.文件编号 = 文件记录表.文件编号
) AS C ) D Where newId between (${obj.sPage} - 1) * ${obj.pageCount} + 1 and ${obj.sPage} * ${obj.pageCount}
`
console.log("有人正在查历史文件记录",obj.uNo)
result = await asyncFun.sqlFun(sql)
res.json(result)
});
主要 order by 最好不要放在子查询中,因为报错