sqlserver2000 +springboot 如何带查询条件进行分页查询？

sqlserver2000分页使用不了offset，原生sql查询出错。

 /**
     * 通过原生 SQL 分页查询返回 List<Map> - 多条记录
     *
     * @param sql 原生 SQL 语句
     * @param pageable Pageable
     * @return List<Map<String, Object>>
     */
    @SuppressWarnings("unchecked")
    public List<Map<String, Object>> findAll(String sql, Pageable pageable) {
        Query query = createResultMapQuery(sql);
        query.setFirstResult((int) pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        return query.getResultList();
    }

修改sql如下：

select top 20 ID,LocType,Pos,BeforeTime,Dept,TrainNum,Operator from MeasureData where 1=1 and  ID not in ( select top 20 ID from MeasureData   where 1=1 ORDER BY BeforeTime desc) order by BeforeTime desc 

使用的原生查询方法：

   /**
     * 创建 Map 查询结果转换器
     *
     * @param sql 原生 SQL 语句
     * @return {@link Query}
     */
    private Query createResultMapQuery(String sql) {
        Query query = _entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query;
    }

 /**
     * 通过原生 SQL 查询返回 List<Map> - 多条记录
     *
     * @param sql 原生 SQL 语句
     * @return List<Map<String, Object>>
     */
    @SuppressWarnings("unchecked")
    public List<Map<String, Object>> findAll(String sql) {
        return createResultMapQuery(sql).getResultList();
    }



    private List<Map<String,Object>> createSql(Pageable pageable, String trainNum, String locType, String[] beforeTime){
        StringBuilder sql =  new StringBuilder();
        sql.append(" select top " + pageable.getPageSize() + " ID,LocType,Pos,BeforeTime,Dept,TrainNum,Operator");
        sql.append(" from MeasureData");
        sql.append(" where 1=1");
        sql.append(" and  ID not in ( select top " +  pageable.getPageSize()*(pageable.getPageNumber()) + " ID from MeasureData  ");
        sql.append(" where 1=1");
        if (StringUtils.isNotEmpty(trainNum)) {
            sql.append(" and TrainNum = '" + trainNum + "'");
        }
        if (StringUtils.isNotEmpty(locType)) {
            sql.append(" and LocType = '" + locType + "'");
        }

        if (beforeTime != null && beforeTime.length > 0 && !StringUtils.equals(beforeTime[0], "null")) {
            sql.append(" and BeforeTime >=  '" + beforeTime[0] + "'");
            sql.append(" and BeforeTime <= '" + beforeTime[1] + "' " );
        }
        sql.append(" ORDER BY BeforeTime desc)");
        if (StringUtils.isNotEmpty(trainNum)) {
            sql.append(" and TrainNum = '" + trainNum + "'");
        }
        if (StringUtils.isNotEmpty(locType)) {
            sql.append(" and LocType = '" + locType + "'");
        }

        if (beforeTime != null && beforeTime.length > 0 && !StringUtils.equals(beforeTime[0], "null")) {
            sql.append(" and BeforeTime >=  '" + beforeTime[0] + "'");
            sql.append(" and BeforeTime <= '" + beforeTime[1] + "' " );
        }

        sql.append(" order by BeforeTime desc ");
        return findAll(sql.toString());
    }

具体格式是参考：

【SQLServer】SQL Server 2000的分页方法(SQL篇) - 泥头 - 博客园

参考使用的第一种方法。需要注意的是因为带有查询条件，所有在第二个select查询语句中也需要把查询条件同外层select查询条件一样添加上，否则查询结果不正确。