SQLServer手写分页查询

由于SQL Server结合Mybatis Plus分页查询耗时过长，简单分页查询用postman测试接口平均2.5s响应时间，手写分页查询后postman测试接口平均100ms响应时间
话不多说，let's do it!


先查总数据量

SELECT COUNT(id) FROM table
<where>
  <if test="……">
       AND ……
  </if>
</where>

再查分页数据

SELECT
	* 
FROM
	( 
SELECT ROW_NUMBER ( ) OVER ( ORDER BY t.create_time DESC) AS rownum, 
t.id,
t.name,
……
FROM table t
<where>
  <if test="……">
       AND ……
  </if>
</where>
) AS tbl 
WHERE
	rownum > (pageNum- 1) * pageSize
	AND rownum <= pageNum * pageSize;

写一个分页展示类

@Getter
@Setter
/**
* 拟名MPage是模仿IPage，M意指微软
*/
public class MPage<T> {

   /**
    * 数据
    */
   private List<T> data;

   /**
    * 页码
    */
   private Integer pageNum;

   /**
    * 每页展示数据量
    */
   private Integer pageSize;

   /**
    * 总数据量
    */
   private Integer total;

   public MPage(List<T> data, Integer pageNum, Integer pageSize, Integer total) {
       this.data = data;
       this.pageNum = pageNum;
       this.pageSize = pageSize;
       this.total = total;
   }

}

OVER!