当前位置：首页 > news >正文

SQL Server分页查询

news 来源：原创 2024/9/20 8:15:18

原生SQL语句，实现SQL Server分页查询

DECLARE @pageCount INT 
DECLARE @pageIndex INT 
SET @pageCount = 10 -- #{page.pageSize}
SET @pageIndex = 2 -- #{page.pageNum}
;WITH ACTE AS (SELECTROW_NUMBER () OVER (ORDER BY id) AS SN,id, table_id, name, workcode, create_time, update_timeFROMlott_result_information),
BCTE AS (SELECT CEILING(MAX(SN) * 1.0 / @pageCount) AS PageTotal FROM ACTE) 
SELECT *,(SELECT COUNT (*) FROM ACTE ) AS total_number,(SELECT PageTotal FROM BCTE ) AS total_page_size 
FROMACTE 
WHEREACTE.SN > (@pageIndex - 1) * @pageCount AND ACTE.SN <= (@pageIndex * @pageCount)

实战

1. 编写Mapper.java的接口

List<LottResultInformation> selectLottResultByPage(@Param("name") String name, @Param("page") BasePage page);

2. 编写Mapper.xml的SQL语句

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.framework.mapper.LottMapper"><resultMap id="BaseResultLottResultMap" type="com.demo.framework.entity.LottResultInformation"  extends="BasePageMap"><id column="id" property="id" jdbcType="INTEGER"/><result column="table_id" property="tableId" jdbcType="INTEGER"/><result column="name" property="name" jdbcType="VARCHAR"/><result column="workcode" property="workcode" jdbcType="VARCHAR"/><result column="create_time" property="createTime" jdbcType="TIMESTAMP"/><result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/></resultMap><resultMap id="BasePageMap" type="com.demo.framework.entity.BasePage"><result column="total_number" property="totalNumber" jdbcType="INTEGER"/><result column="total_page_size" property="pageTotal" jdbcType="INTEGER"/></resultMap><select id="selectLottResultByPage" resultMap="BaseResultLottResultMap">DECLARE @pageCount INTDECLARE @pageIndex INTSET @pageCount = #{page.pageSize}SET @pageIndex = #{page.pageNum};WITH ACTE AS (SELECT ROW_NUMBER() OVER (ORDER BY id) AS SN,id, table_id, name, workcode, create_time, update_timeFROM lott_result_information<if test="name != null and name !=''">where name LIKE CONCAT('%',#{name},'%')</if>),BCTE AS (SELECT CEILING(MAX(SN) * 1.0 / @pageCount) AS PageTotal FROM ACTE)SELECT *,(SELECT COUNT(*) FROM ACTE)  AS total_number,(SELECT PageTotal FROM BCTE) AS total_page_sizeFROM ACTEWHERE ACTE.SN > (@pageIndex - 1) * @pageCountAND ACTE.SN &lt;= (@pageIndex * @pageCount)</select></mapper>

这样的话每次查询都会冗余两列total_number和total_page_size；这样也方便每次获取总页数和总条数。