PageHelper下的Sqlserver分页查询错误

首先

 public PageInfo<RobotBlacklistVo> list(RobotBlacklistBo robotBlacklistBo) {
        PageHelper.startPage(robotBlacklistBo.getPage(), robotBlacklistBo.getSize());
        List<RobotBlacklistVo> leveJobChatRoomLogPos = v2RobotBlacklistMapper.selectByPage(robotBlacklistBo);
        return new PageInfo<>(leveJobChatRoomLogPos);
    }

对应的xml文件

SELECT rb.nId,
        rb.vcRobotWxId,
        r.vcNickName as vcRobotWxName,
        rb.vcRemark
        FROM BAS_RobotBlackList rb
        LEFT JOIN Robot r
        on rb.vcRobotWxId = r.vcRobotWxId
        <if test="merchantNo != null and merchantNo != ''">
            left join BAS_MerchantRobot mr on mr.vcRobotWxId = r.vcRobotWxId
        </if>
        <where>
            <if test="merchantNo != null and merchantNo != ''">
                and mr.vcMerchantNo = #{merchantNo}
                and mr.nStatus = 10
            </if>
            <if test="vcRobotWxId != null and vcRobotWxId != ''">
                and rb.vcRobotWxId = #{vcRobotWxId}
            </if>
            <if test="vcRobotWxName != null and vcRobotWxName != ''">
                and r.vcNickName like '%'+#{vcRobotWxName}+'%'
            </if>
            <if test="vcUserWxId != null and vcUserWxId != ''">
                and rb.vcUserWxId = #{vcUserWxId}
            </if>
            <if test="vcNickName != null and vcNickName != ''">
                and rb.vcNickName like '%'+#{vcNickName}+'%'
            </if>
            <if test="nIsCancel != null">
                and rb.nIsCancel = #{nIsCancel}
            </if>
            <if test="begin != null">
                and rb.dtCreateDate > #{begin}
            </if>
            <if test="end != null">
                and rb.dtCreateDate &lt; #{end}
            </if>
        </where>
        ORDER BY nId DESC
    </select>

当不写with(nolock)时候查询是没错的，但是要求查询不锁表，
加上with(nolock)就报错

image.png

百度了一番，说加上
指定具体数目
我们可以用具体的数字里进行限量获取，这个和mysql的limit关键字有点像。这里用一个sql来展示从表中获取第20个之后的10个数据：

SELECT  TOP  10 * 
FROM  test2
WHERE  id NOT IN( SELECT top  20 id FROM  test2 )

结果报错更改了

image.png

最终选择自己查询分页的条数。