一个比较好用的SQL分页查询
一个比较好用的SQL分页查询
现在公司里用的分页存储过程，执行效率还行，不知道原作者是谁
CREATE PROCEDURE dbo.P_viewPage_A
/*
nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7 QQ:34813284
敬告：适用于单一主键或存在唯一值列的表或视图
ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围
*/
@TableName VARCHAR(200), --表名
@FieldList VARCHAR(2000), --显示列名，如果是全部字段则为*
@PrimaryKey VARCHAR(100), --单一主键或唯一值键
@Where VARCHAR(2000), --查询条件 不含'where'字符，如id>10 and len(userid)>9
@Order VARCHAR(1000), --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc
--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
@SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
@RecorderCount INT, --记录总数 0:会返回总记录
@PageSize INT, --每页输出的记录数
@PageIndex INT, --当前页数
@TotalCount INT OUTPUT, --记返回总记录
@TotalPageCount INT OUTPUT --返回总页数
AS
SET NOCOUNT ON
IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0
SET @Order = RTRIM(LTRIM(@Order))
SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')
WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0
BEGIN SET @Order = REPLACE(@Order,', ',',')
SET @Order = REPLACE(@Order,' ,',',')
END
IF ISNULL(@TableName,'') = ''
OR ISNULL(@FieldList,'') = ''
OR ISNULL(@PrimaryKey,'') = ''
OR @SortType < 1 OR @SortType >3
OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0
BEGIN
PRINT('ERR_00')
RETURN
END
IF @SortType = 3
BEGIN
IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')
BEGIN PRINT('ERR_02') RETURN END
END
DECLARE @new_where1 VARCHAR(1000)
DECLARE @new_where2 VARCHAR(1000)
DECLARE @new_order1 VARCHAR(1000)
DECLARE @new_order2 VARCHAR(1000)
DECLARE @new_order3 VARCHAR(1000)
DECLARE @Sql VARCHAR(8000)
DECLARE @SqlCount NVARCHAR(4000)
IF ISNULL(@where,'') = ''
BEGIN
SET @new_where1 = ' '
SET @new_where2 = ' WHERE '
END ELSE
BEGIN
SET @new_where1 = ' WHERE ' + @where
SET @new_where2 = ' WHERE ' + @where + ' AND '
END
IF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2
BEGIN
IF @SortType = 1
BEGIN
SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC'
SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC'
END
IF @SortType = 2
BEGIN
SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC'
SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC'
END
END
ELSE
BEGIN
SET @new_order1 = ' ORDER BY ' + @Order
END
IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0
BEGIN
SET @new_order1 = ' ORDER BY ' + @Order
SET @new_order2 = @Order + ','
SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')
SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
IF @FieldList <> '*'
BEGIN SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')
SET @FieldList = ',' + @FieldList
WHILE CHARINDEX(',',@new_order3)>0
BEGIN
IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0
BEGIN
SET @FieldList =
@FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))
END
SET @new_order3 =
SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))
END
SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))
END
END
SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1
IF @RecorderCount = 0
BEGIN
EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',@TotalCount OUTPUT,@TotalPageCount OUTPUT
END
ELSE
BEGIN
SELECT @TotalCount = @RecorderCount
END
IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
END
IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
IF @PageIndex = 1 --返回第一页数据
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order1
END
IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
+ ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1
END
END
ELSE
BEGIN
IF @SortType = 1 --仅主键正序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' > '
MySQL、Oracle和SQLServer的分页查询语句
MySQL、Oracle和SQLServer的分页查询语句
假设当前是第PageNo页，每页有PageSize条记录，现在分别⽤Mysql、Oracle和SQL Server分页查询student表。
1、Mysql的分页查询：
1 SELECT
2 *
3 FROM
4 student
5 LIMIT (PageNo - 1) * PageSize,PageSize;
理解：(Limit n,m) =>从第n⾏开始取m条记录，n从0开始算。
2、Oracel的分页查询：
1 SELECT
2 *
3 FROM
4 (
5 SELECT
6 ROWNUM rn ,*
7 FROM
8 student
9 WHERE
10 Rownum <= pageNo * pageSize
11 )
12 WHERE
13 rn > (pageNo - 1) * pageSize
理解：假设pageNo = 1，pageSize = 10，先从student表取出⾏号⼩于等于10的记录，然后再从这些记录取出rn⼤于0的记
录，从⽽达到分页⽬的。ROWNUM从1开始。
3、SQL Server分页查询：
1 SELECT
2 TOP PageSize *
3 FROM
4 (
5 SELECT
6 ROW_NUMBER () OVER (ORDER BY id ASC) RowNumber ,*
7 FROM
8 student
9 ) A
10 WHERE
11 A.RowNumber > (PageNo - 1) * PageSize
理解：假设pageNo = 1，pageSize = 10，先按照student表的id升序排序，rownumber作为⾏号，然后再取出从第1⾏开始
的10条记录。
分页查询有的数据库可能有⼏种⽅式，这⾥写的可能也不是效率最⾼的查询⽅式，但这是我⽤的最顺⼿的分页查询，如
果有兴趣也可以对其他的分页查询的⽅式研究⼀下。
mybatis sqlserver分页查询语句
mybatis sqlserver分页查询语句
摘要：
1.MyBatis简介
2.SQL Server分页查询基础知识
3.MyBatis分页查询实现方法
4.示例代码及解析
5.总结与建议
正文：
一、MyBatis简介
MyBatis是一款优秀的持久层框架，它支持定制化SQL、存储过程以及高级映射。MyBatis避免了几乎所有的JDBC代码和手动设置参数以及获取结果集，可以让开发者专注于SQL本身，提高了开发效率。
二、SQL Server分页查询基础知识
在SQL Server中，常用的分页查询方法有两种：一种是使用ROW_NUMBER()窗口函数，另一种是使用OFFSET FETCH NEXT子句。
1.使用ROW_NUMBER()窗口函数
```
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY column_name) AS row_num
FROM table_name )
SELECT * FROM CTE WHERE row_num BETWEEN start_number AND
end_number
```
2.使用OFFSET FETCH NEXT子句
```
SELECT * FROM table_name
ORDER BY column_name
OFFSET start_number ROWS
FETCH NEXT rows_per_page ROWS ONLY
```
三、MyBatis分页查询实现方法
在MyBatis中，可以通过编写自定义的SQL映射语句或者使用MyBatis提供的分页插件来实现分页查询。
1.编写自定义SQL映射语句
```xml
SELECT * FROM (
SELECT t.*,
IF(@lastPage = 1, 0, @lastPage := @lastPage + 1) AS
SQLServer数据分页查询
SQLServer数据分页查询
最近学习了⼀下SQL的分页查询，总结了以下⼏种⽅法。⾸先建⽴了⼀个表，随意插⼊的⼀些测试数据，表结构和数据如下图：
现在假设我们要做的是每页5条数据，⽽现在我们要取第三页的数据。（数据太少，就每页5条了）
⽅法⼀：
select top 5 *
from [StuDB].[dbo].[ScoreInfo]
where [SID] not in
(select top 10 [SID]
from [StuDB].[dbo].[ScoreInfo]
order by [SID])
order by [SID]结果：
此⽅法是先取出前10条的SID(前两页)，排除前10条数据的SID，然后在剩下的数据⾥⾯取出前5条数据。
缺点就是它会遍历表中所有数据两次，数据量⼤时性能不好。
⽅法⼆：
select top 5 *
from [StuDB].[dbo].[ScoreInfo]
where [SID]>
(select MAX(t.[SID]) from (select top 10 [SID] from [StuDB].[dbo].[ScoreInfo] order by [SID]) t )
order by [SID]
结果：
此⽅法是先取出前10条数据的SID，然后取出SID的最⼤值，再从数据⾥⾯取出 ⼤于 前10条SID的最⼤值 的前5条数据。
缺点是性能⽐较差，和⽅法⼀⼤同⼩异。
⽅法三：
select *
from (select *,ROW_NUMBER() over(order by [SID]) ROW_ID from [StuDB].[dbo].[ScoreInfo]) t
where t.[SID] between (5*(3-1)+1) and 5*3结果：
此⽅法的特点就是使⽤ ROW_NUMBER() 函数，这个⽅法性能⽐前两种⽅法要好，只会遍历⼀次所有的数据。适⽤于Sql Server 2000之后
mybatis sqlserver分页查询语句 2
第 1 页 共 2 页 mybatis sqlserver分页查询语句
（原创实用版）
目录
1.MyBatis 简介
2.SQL Server 分页查询原理
3.MyBatis 实现 SQL Server 分页查询的方法
4.示例代码
正文
【MyBatis 简介】
MyBatis 是一个优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解进行配置和原生映射，将接口和 Java 的 POJO（Plain Old Java Objects，普通的 Java 对象）映射成数据库中的记录。
【SQL Server 分页查询原理】
在 SQL Server 中，分页查询是通过使用 ROW_NUMBER() 窗口函数或者使用 OFFSET 和 FETCH NEXT 关键字实现的。ROW_NUMBER() 函数可以为每一行数据分配一个唯一的序号，然后通过筛选出指定范围内的序号，从而实现分页查询。而 OFFSET 和 FETCH NEXT 关键字则是通过指定跳过的行数和要返回的行数来实现分页查询。
【MyBatis 实现 SQL Server 分页查询的方法】
在 MyBatis 中，可以通过编写自定义的 SQL 映射文件或者使用动态
SQL 来实现 SQL Server 分页查询。
方法一：编写自定义的 SQL 映射文件 第 2 页 共 2 页 在映射文件中，可以通过 ROW_NUMBER() 函数或者 OFFSET 和
FETCH NEXT 关键字实现分页查询。以下是使用 ROW_NUMBER() 函数的示例：
```xml
SELECT
ROW_NUMBER() OVER(ORDER BY id) AS rowNum,
SQL实现分页查询方法总结
SQL实现分页查询⽅法总结
开发过程中经常遇到分页的需求，今天在此总结⼀下吧。
简单说来⽅法有两种，⼀种在源上控制，⼀种在端上控制。源上控制把分页逻辑放在SQL层；端上控制⼀次性获取所有数据，
把分页逻辑放在UI上（如GridView）。显然，端上控制开发难度低，适于⼩规模数据，但数据量增⼤时性能和IO消耗⽆法接
受；源上控制在性能和开发难度上较为平衡，适应⼤多数业务场景；除此之外，还可以根据客观情况（性能要求，源与端的资
源占⽤等）在源和端之间加⼀层，应⽤特殊算法和技术进⾏处理。以下主要讨论源上，即SQL上的分页。
分页的问题其实就是在满⾜条件的⼀堆有序数据中截取当前所需要展⽰的那部分。实际上各种数据库都考虑到分页问题⽽内置
了⼀些策略，⽐如MySql的LIMIT，Oracle的ROWNUM和ROW_NUMBER()，SqlServer的TOP和ROW_NUMBER()，基于此
我们可以得到⼀系列分页的⽅法。
1、 基于MySql的LIMIT和Oracle的ROWNUM，可以直接限制返回区间（以MySql为例，注意使⽤
Oracle的ROWNUM时要应⽤⼦查询）：
⽅法⼀、直接限制返回区间
SELECT * FROM table WHERE 查询条件 ORDER BY 排序条件 LIMIT ((页码-1)*页⼤⼩),页⼤⼩;
优点：写法简单。
缺点：当页码和页⼤⼩过⼤时，性能明显下降。
适⽤：数据量不⼤。
2、基于LIMIT（MySql）、ROWNUM（Oracle）和TOP（SqlServer），他们可以限制返回的⾏
数，因此可以得到以下两套通⽤的⽅法（以SqlServer为例）：
⽅法⼆、NOT IN
SELECT TOP 页⼤⼩ * FROM table WHERE 主键 NOT IN
(
SELECT TOP (页码-1)*页⼤⼩ 主键 FROM table WHERE 查询条件 ORDER BY 排序条件
)
ORDER BY 排序条件
一个通用的sql分页查询语句
⼀个通⽤的sql分页查询语句
代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/*
===================================================================
名称：sp_GetRecordFromPage
功能：千万级记录通⽤查询分页功能
输⼊参数：
@tblName varchar(1000), -- 表名
@SelectFieldName varchar(4000), -- 要显⽰的字段名(不要加select)字段名⽤,隔开
@strWhere varchar(4000), -- 查询条件(注意: 不要加 where)
@OrderFieldName varchar(255), -- 排序索引字段名
@PageSize int , -- 页⼤⼩
@PageIndex int = 1, -- 页码
@iRowCount int output, -- 返回记录总数
@OrderType bit = 0 -- 设置排序类型, ⾮ 0 值则降序
返回：此条件的记录总数
适⽤的表：任意表
影响的表:任意表
⽇期:2004-12-01 14:52
作者:xiemail
⽇期:2009-9-11 14:52
作者:ykaiyong 使⽤ROW_NUMBER，解决排序字段重复值引起的bug
备注：
===================================================================
*/
ALTER PROCEDURE [dbo].[sp_GetRecordFromPage]
(
@tblName varchar(1000), -- 表名
@SelectFieldName varchar(4000), -- 要显⽰的字段名(不要加select)字段名⽤,隔开
@strWhere varchar(4000), -- 查询条件(注意: 不要加 where)
mysqlplus 分页sql语句
mysqlplus 分页sql语句
MySQLPlus 是一个基于 Python 的 MySQL 数据库访问库，用于连接和操作 MySQL 数据库。要实现分页查询，你可以使用 SQL 语句中的
LIMIT 子句来限制结果集的数量，并结合 OFFSET 子句来跳过前面的结果。
下面是一个示例的分页 SQL 语句：
sql
SELECT * FROM table_name LIMIT page_size OFFSET start_row;
其中，table_name 是你要查询的表名，page_size 是每页显示的记录数，start_row 是起始记录的索引。
例如，如果你希望每页显示 10 条记录，从第 21 条记录开始显示，那么 SQL 语句将如下所示：
sql
SELECT * FROM table_name LIMIT 10 OFFSET 20;
这将返回从第 21 条记录开始的 10 条记录。
你可以在 Python 中使用 MySQLPlus 来执行这个 SQL 语句。以下是一个简单的示例：
python
import mysql.connector
from mysql.connector import Error
def fetch_data(table_name, page_size, start_row):
try:
connection = mysql.connector.connect(
host='your_host',
database='your_database',
user='your_username',
password='your_password'
)
if connection.is_connected():
SqlServer三种分页查询语句
SqlServer三种分页查询语句
SqlServer 的三种分页查询语句
先说好吧，查询的数据排序，有两个地⽅（1、分页前的排序。2、查询到当前页数据后的排序）
第⼀种、
1、 先查询当前页码之前的所有数据id
select top ((当前页数-1)*每页数据条数) id from 表名
2、再查询所有数据的前⼏条，但是id不在之前查出来的数据中
select top 每页数据条数 * from 表名 where id not in ( select top ((当前页数-1)*每页数据条数) id from 表名 )
3、查询出当前页⾯的所有数据后，再根据⼀列数据进⾏排序
select * from (
select top 每页数据条数 * from 表名 where id not in (select top ((当前页数-1)*每页数据条数) id from 表名)
) as b order by 排序列名 desc
4、当然，如果想要修改排序列再查询也可以（默认是按照id asc 排序的，我们可以改为其他列）
select top 每页数据条数 * from 表名 where id not in (select top ((2-1)*5) id from wg_users order by 排序列名 desc) order by 排序列名 desc
这⾥的排序列名⼀定要⽤同⼀列，不然的话，分页查询就会查出重复数据或者少数据，因为排序错乱的原因
第⼆种、ROW_NUMBER()分页
1、使⽤ROW_NUMBER()函数先给查询到的所有数据添加⼀列序号（就是给数据加⼀列1、2、3、4、5......这个，⼀定不要去掉后⾯起的那个别名【我这⾥叫做b】）
select * from (select ROW_NUMBER() OVER(Order by id) AS RowNumber,* from 表名) as b
各种数据库分页查询SQL
一、DB2:
DB2分页查询
SELECT * FROM (Select 字段1,字段2,字段3,rownumber() over(ORDER BY 排序用的列名 ASC) AS rn from 表名) AS a1 WHERE a1.rn BETWEEN 10 AND 20
以上表示提取第10到20的纪录
select * from (select rownumber() over(order by id asc ) as rowid from table where rowid
<=endIndex ) where rowid > startIndex
如果Order By 的字段有重复的值，那一定要把此字段放到 over()中
select * from ( select ROW_NUMBER() OVER(ORDER BY DOC_UUID DESC) AS ROWNUM,
DOC_UUID, DOC_DISPATCHORG, DOC_SIGNER, DOC_TITLE from
DT_DOCUMENT ) a where ROWNUM > 20 and ROWNUM <=30
增加行号，不排序
select * from ( select ROW_NUMBER() OVER() AS ROWNUM,t.* from DT_DOCUMENT t )
a
增加行号，按某列排序
select * from ( select ROW_NUMBER() OVER( ORDER BY DOC_UUID DESC ) AS
ROWNUM,t.* from DT_DOCUMENT t ) a
二、Mysql:
最简单
select * from table limit start,pageNum
比如从10取20个数据
真正高效的SQLSERVER分页查询
真正⾼效的SQLSERVER分页查询
Sqlserver数据库分页查询⼀直是Sqlserver的短板，闲来⽆事，想出⼏种⽅法，假设有表ARTICLE,字段ID、YEAR...(其他省略)，数据53210条
(客户真实数据，量不⼤)，分页查询每页30条，查询第1500页（即第45001-45030条数据），字段ID聚集索引，YEAR⽆索引，Sqlserver版
本：2008R2
第⼀种⽅案、最简单、普通的⽅法：
SELECT TOP 30 * FROM ARTICLE WHERE ID NOT IN(SELECT TOP 45000 ID FROM ARTICLE ORDER BY YEAR DESC, ID DESC) ORDER BY YEAR DESC,ID DESC
平均查询100次所需时间：45s
第⼆种⽅案：
SELECT * FROM
(
SELECT TOP 30 * FROM (SELECT TOP 45030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) f ORDER BY f.YEAR ASC, f.ID DESC) s ORDER BY s.YEAR DESC,s.ID DESC
平均查询100次所需时间：138S
第三种⽅案：
SELECT * FROM ARTICLE w1,
(
SELECT TOP 30 ID FROM
(
SELECT TOP 50030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
) w ORDER BY w.YEAR ASC, w.ID ASC
) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC, w1.ID DESC
平均查询100次所需时间：21S
第四种⽅案：
SELECT * FROM ARTICLE w1
WHERE ID in