SQL Server分页

环境

基于Mybatis动态SQL
使用fastjson格式化输出数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Repository
public interface StudentDao {
List<Map<String, Object>> getStudent(@Param("sex") String sex,
@Param("page") int page, @Param("pageSize") int pageSize);
}

public class MyTest {
private StudentDao dao;
public void test() {
// 查找性别为男, 分页大小为10, 的第2页数据
List<Map<String, Object>> data = dao.getStudent("男", 2, 10);
System.out.println(JSONObject.toJSONString(data));
}
}

SQL Server 2005

在2005之后, 使用ROW_NUMBER()函数标记记录的行号, 然后再使用where进行筛选。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<select id="getStudent" resultType="Map">
<bind name="skip" value="(page-1)*pageSize"/>
<bind name="record" value="page*pageSize"/>
SELECT *
FROM (
SELECT TOP 100 PERCENT tmp.*,
ROW_NUMBER() OVER (ORDER BY tmp.birthday desc) AS row_num
FROM (
SELECT DISTINCT s.id, s.name, CONVERT (VARCHAR(10), s.birthday, 120) AS birthday
FROM student s
WHERE s.sex = ${sex}
ORDER BY s.birthday
) tmp
ORDER BY row_num
) tmp
<![CDATA[WHERE row_num > ${skip} and row_num <= ${record}]]>
</select>

SQL Server 2012

在2012之后, 使用OFFSET FETCH 子句进行分页处理。

1
2
3
4
5
6
7
8
<select id="getStudent" resultType="Map">
<bind name="skip" value="(page-1)*pageSize"/>
SELECT DISTINCT s.id, s.name, CONVERT (VARCHAR(10), s.birthday, 120) AS birthday
FROM student s
WHERE s.sex = ${sex}
ORDER BY s.birthday
OFFSET ${skip} ROWS FETCH NEXT ${pageSize} ROWS ONLY;
</select>