首页 > 数据库 >SQL Server下7种“数据分页”方案，全网最全

SQL Server下7种“数据分页”方案，全网最全

时间：2023-01-03 13:56:24浏览次数：64  
标签：10 pageSize menuId 最全 Server -- SQL TOP SELECT

数据分页往往有三种常用方案。

第一种，把数据库中存放的相关数据，全部读入PHP/Java/C#代码/内存，再由代码对其进行分页操作（速度慢，简易性高）。

第二种，直接在数据库中对相关数据进行分页操作，再把分页后的数据输出给代码程序（速度中，简易性中）。

第三种，先把数据库中的相关数据全部读入“缓存”或第三方工具，再由代码程序对“缓存”或第三方工具中的数据进行读取+分页操作（速度快，简易性差）。

 

本文下面重点阐述上述【第二种】方案在SQL Server上的使用（其它种类数据库由于Sql语句略有差异，所以需要调整，但方案也类似）

 

1、ROW_NUMBER() OVER()方式（SQL2012以下推荐使用）

示例：

SELECT * FROM    (SELECT ROW_NUMBER() OVER(ORDER BY menuId) AS RowId,* FROM sys_menu ) AS r WHERE  RowId BETWEEN 1 AND 10

用子查询新增一列行号（ROW_NUMBER）RowId查询，比较高效的查询方式，只有在SQL Server2005或更高版本才支持。

BETWEEN 1 AND 10 是指查询第1到第10条数据（闭区间），在这里面需要注意的是OVER的括号里面可以写多个排序字段。

通用用法

--pageIndex 表示指定页--pageSize  表示每页显示的条数SELECT * FROM    (SELECT ROW_NUMBER() OVER(ORDER BY 排序字段) AS RowId,* FROM 表名 ) AS r WHERE  RowId  BETWEEN ((pageIndex-1)*pageSize + 1) AND (pageIndex * PageSize)

 

2、offset fetch next方式（SQL2012及以上的版本才支持：推荐使用 ）

示例：

--offset fetch next方式查询，最高效的查询方式，只有在SQL Server2012或更高版本才支持SELECT * FROM sys_menu ORDER BY menuId offset 0 ROWS FETCH NEXT 10 ROWS ONLY

offset 是跳过多少行，

next是取接下来的多少行，

 

句式 offset...rows fetch nect ..rows only ，注意rows和末尾的only 不要写漏掉了，并且这种方式必须要接着Order by XX 使用，不然会报错。

通用用法

--pageIndex 表示指定页--pageSize  表示每页显示的条数SELECT * FROM 表名 ORDER BY 排序字段 offset ((pageIndex - 1) * pageSize) ROWS FETCH NEXT pageSize ROWS ONLY

3、top not in方式 （不推荐）

示例：

--查询第11-20条记录SELECT TOP 10 menuId, *FROM sys_menu WHERE menuId NOT IN (SELECT TOP 10 menuId FROM sys_menu)

这条语句的原理是先查询1-10条记录的ID，然后再查询ID不属于这1-10条记录的ID，并且只需要10条记录，因为每页大小就是10，

这就是获取到的第11-20条记录，这是非常简单的一种写法。

 

另外IN语句与NOT IN语句类似，这是NOT IN的写法，但是这种写法数据量大的话效率太低。

通用用法

--pageIndex 表示指定页--pageSize  表示每页显示的条数SELECT TOP pageSize menuId, *FROM sys_menu WHERE menuId NOT IN (SELECT TOP ((pageSize-1)*pageIndex) menuId FROM sys_menu)

4、通过升序与降序方式进行查询分页（不推荐）

示例：

--查询第11-20条记录SELECT * FROM(    SELECT TOP 10 * FROM(        SELECT TOP 20 * FROM sys_menu ORDER BY menuId ASC)             AS TEMP1 ORDER BY menuId DESC)        AS TEMP2 ORDER BY menuId ASC

这条语句首先查询前20条记录，然后在倒序查询前10条记录（即倒数10条记录），

 

这个时候就已经获取到了11-20条记录，但是他们的顺序是倒序，所以最后又进行升序排序。

通用方法

--pageIndex 表示指定页--pageSize  表示每页显示的条数SELECT * FROM(    SELECT TOP pageSize * FROM(        SELECT TOP ((pageIndex - 1) * pageSize +(pageSize*2)) * FROM sys_menu ORDER BY menuId ASC)             AS TEMP1 ORDER BY menuId DESC)        AS TEMP2 ORDER BY menuId ASC

5、采用MAX(ID)或者MIN(ID)函数（不推荐）

示例：

--查询第11-20条记录SELECT TOP 10 * FROM sys_menu WHERE menuId>    (SELECT MAX(menuId) FROM(SELECT TOP 10 menuId FROM sys_menu ORDER BY menuId) AS TEMP1) --（第10条的id）

这个理解起来也简单，先把第10条记录的id找出来（当然这里面是直接使用MAX()进行查找，MIN()函数的用法也是类似的），

然后再对比取比第10条记录的id大的前10条记录即为我们需要的结果。

 

这里要注意开始时的边界值调整。

通用用法

--pageIndex 表示指定页--pageSize  表示每页显示的条数SELECT TOP pageSize * FROM sys_menu WHERE menuId>    (SELECT MAX(menuId) FROM(SELECT TOP ((PageIndex-1)*PageSize) menuId FROM sys_menu ORDER BY menuId) AS TEMP1) --（第10条的id）

 

上述1~5方案，再配合存储过程，你就能制造出适合你自己的“分页”轮子，日后反复使用。

但它们有一定自身局限性：方案1、2、5都需要依赖一个排序Id（这个Id要么是个排序列，要么是个主键）。方案3、4则效率太低，完全不推荐。

 

7、不依赖排序/排序Id的终极方案

此方案在DeveloperSharp框架中有提供（基于.Net/.Net Core/.Net Framework），方案被广东省的多个公司/项目采用，得到了实战验证+稳定性。

【第一步】：从NuGet引用DeveloperSharp包。

【第二步】：创建一个用来与数据库进行通信的“数据源类”（文本示例为：TestData.cs），内容如下：

using DeveloperSharp.Structure.Model;
using DeveloperSharp.Framework.QueryEngine;

namespace YZZ
{
    [DataSource(DatabaseType.SQLServer, "Server=localhost;Database=Test;Uid=sa;Pwd=123")]
    public class TestData : DeveloperSharp.Structure.Model.DataLayer
    {
        //类中没有任何代码
    }
}

说 明 ：“数据源类”（文本示例为：TestData.cs）必 须 继 承 自 DeveloperSharp.Structure.Model.DataLayer 类 ， 并 且 在 其 上 设 置DataSource属 性 的 初 始 化 值 为“数据库类型”及其“链接字符串”。

 

【第三步】：添加通过“数据源类”（TestData）调用其PagePartition方法进行数据分页的代码。注 意：核心代码就一行而已！！

代码如下：

using DeveloperSharp.Extension;//Table扩展所在的命名空间
-----------------------------
    class Program
    {
        static void Main(string[] args)
        {
            TestData td = new TestData();

            //分页
            var pp = td.PagePartition("select top 5000 * from t_Order where Id>10 order by Id desc", 20, 162);

            List<Product> Products = pp.Table.ToList<Product>();
            foreach (var P in Products)
            {
                Console.WriteLine(P.Name);
            }

            Console.ReadLine();
        }
    }

Product类代码如下：

    public class Product
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public int Quantity { get; set; }
    }

 

此处的PagePartition方法有两个重载方法，其详细功能说明如下：

PagePartition
声明：public PagePiece PagePartition(string RecordSet, string Id, int PageSize, int PageIndex)
用途：分页功能(有主键)
参数：（1）string RecordSet     --需要分页的记录集，可以是表、视图、或者SQL语句
（2）string Id     --主键
（3）int PageSize     --页面大小
（4）int PageIndex     --当前页码
返回：PagePiece  --页片实体

PagePartition
声明：public PagePiece PagePartition(string RecordSet, int PageSize, int PageIndex)
用途：分页功能(无主键)
参数：（1）string RecordSet     -- 需要分页的记录集，可以是表、视图、或者SQL语句
     （2）int PageSize    --页面大小
（3）int PageIndex    --当前页码
返回：PagePiece  --页片实体

注意：

（1）     当你需要分页的数据表有“主键”字段时，使用“分页功能(有主键)”。反之，使用“分页功能(无主键)”。

（2）     RecordSet是你需要分页的“数据总集”的SQL语句。该SQL语句的形式丰富多样，可以带条件、排序、甚至还能是多表的联合查询、等。

（3）     此方法符合最开始的【第二种】方案，是在SQL Server内部进行的分页操作。而且可以不依赖于排序/排序Id。

 

本文部分内容参考自公众号：熊泽有话说，在此向号主表达感谢。

原文链接：http://www.developersharp.cc/content8.html

服务条款：http://www.developersharp.cc/buy.html

运行有问题，需要技术支持？请添加微信：894988403

运行有问题，需要技术支持？请添加微信：894988403

 

1、ROW_NUMBER() OVER()方式（SQL2012以下推荐使用）

示例：

SELECT * FROM    (SELECT ROW_NUMBER() OVER(ORDER BY menuId) AS RowId,* FROM sys_menu ) AS r WHERE  RowId BETWEEN 1 AND 10

用子查询新增一列行号（ROW_NUMBER）RowId查询，比较高效的查询方式，只有在SQL Server2005或更高版本才支持。

BETWEEN 1 AND 10 是指查询第1到第10条数据（闭区间），在这里面需要注意的是OVER的括号里面可以写多个排序字段。

查询结果如下：

图片

通用用法

--pageIndex 表示指定页--pageSize  表示每页显示的条数SELECT * FROM    (SELECT ROW_NUMBER() OVER(ORDER BY 排序字段) AS RowId,* FROM 表名 ) AS r WHERE  RowId  BETWEEN ((pageIndex-1)*pageSize + 1) AND (pageIndex * PageSize)

2、offset fetch next方式（SQL2012及以上的版本才支持：推荐使用 ）

示例：

--offset fetch next方式查询，最高效的查询方式，只有在SQL Server2012或更高版本才支持SELECT * FROM sys_menu ORDER BY menuId offset 0 ROWS FETCH NEXT 10 ROWS ONLY

offset 是跳过多少行，

next是取接下来的多少行，

句式 offset...rows fetch nect ..rows only ，注意rows和末尾的only 不要写漏掉了，并且这种方式必须要接着Order by XX 使用，不然会报错。

查询结果如下：

图片

通用用法

--pageIndex 表示指定页--pageSize  表示每页显示的条数SELECT * FROM 表名 ORDER BY 排序字段 offset ((pageIndex - 1) * pageSize) ROWS FETCH NEXT pageSize ROWS ONLY

3、top not in方式 （不推荐）

示例：

--查询第11-20条记录SELECT TOP 10 menuId, *FROM sys_menu WHERE menuId NOT IN (SELECT TOP 10 menuId FROM sys_menu)

这条语句的原理是先查询1-10条记录的ID，然后再查询ID不属于这1-10条记录的ID，并且只需要10条记录，因为每页大小就是10，

这就是获取到的第11-20条记录，这是非常简单的一种写法。

另外IN语句与NOT IN语句类似，这是NOT IN的写法，但是这种写法数据量大的话效率太低。

查询结果如下：

图片

通用用法

--pageIndex 表示指定页--pageSize  表示每页显示的条数SELECT TOP pageSize menuId, *FROM sys_menu WHERE menuId NOT IN (SELECT TOP ((pageSize-1)*pageIndex) menuId FROM sys_menu)

4、通过升序与降序方式进行查询分页（不推荐）

示例：

--使用升序降序的方式分页查询SELECT * FROM(    SELECT TOP 10 * FROM(        SELECT TOP 20 * FROM sys_menu ORDER BY menuId ASC)             AS TEMP1 ORDER BY menuId DESC)        AS TEMP2 ORDER BY menuId ASC

这条语句首先查询前20条记录，然后在倒序查询前10条记录（即倒数10条记录），

这个时候就已经获取到了11-20条记录，但是他们的顺序是倒序，所以最后又进行升序排序。

查询结果如下：

图片

通用方法

--pageIndex 表示指定页--pageSize  表示每页显示的条数SELECT * FROM(    SELECT TOP pageSize * FROM(        SELECT TOP ((pageIndex - 1) * pageSize +(pageSize*2)) * FROM sys_menu ORDER BY menuId ASC)             AS TEMP1 ORDER BY menuId DESC)        AS TEMP2 ORDER BY menuId ASC

5、采用MAX(ID)或者MIN(ID)函数（不推荐）

示例：

--MIN()函数和MAX()函数的使用--id > 第(PageIndex-1)*PageSize条记录的id AND id <= 第PageIndex*PageSize条记录的idSELECT TOP 10 * FROM sys_menu WHERE menuId>    (SELECT MAX(menuId) FROM(SELECT TOP 10 menuId FROM sys_menu ORDER BY menuId) AS TEMP1) --（第10条的id）

这个理解起来也简单，先把第10条记录的id找出来（当然这里面是直接使用MAX()进行查找，MIN()函数的用法也是类似的），

然后再对比取比第10条记录的id大的前10条记录即为我们需要的结果。

这里要注意开始时的边界值调整。

查询结果如下：

图片

通用用法

--pageIndex 表示指定页--pageSize  表示每页显示的条数SELECT TOP pageSize * FROM sys_menu WHERE menuId>    (SELECT MAX(menuId) FROM(SELECT TOP ((PageIndex-1)*PageSize) menuId FROM sys_menu ORDER BY menuId) AS TEMP1) --（第10条的id）

6、Lambda表达式分页（推荐使用）

我们在数据库分页的时候，还可以在代码里面使用lambda表达式分页。

示例：

List<int> list = new List<int>();for (int i = 0; i < 100; i++){    list.Add(i);}//从第11条数据开始，获取10条数据list = list.Skip(11).Take(10).ToList(); //返回值 11，12,13,14,15，16,17,18,19,20

Skip： 表示从第 （pageIndex * pageSize + 1） 条数据开始，也就是说再这之前有pageIndex * pageSize条数据。

Task：表示获取多少条数据。

通用用法

list = list.Skip(pageIndex * pageSize +1 ).Take(pageSize).ToList();

以上就是数据查询中经常用到的方式，

在数据库版本支持的情况下个人推荐程度排序：offset fetch netct  > lambda  > ROW_NUMBER() OVER() ，后面的就不推荐使用 。

这样就可以配合存储过程进行分页了。

标签：10,pageSize,menuId,最全,Server,--,SQL,TOP,SELECT
From： https://www.cnblogs.com/dotnet-college/p/17021701.html

相关文章

  • MySQL压缩版安装与卸载
    MySQL压缩版安装与卸载安装​ 1、压缩包下载地址：https://dev.mysql.com/downloads/mysql/​ 选择Windows(x86，64-bit)，ZIPArchive版本即可​ 2、下载后解压​ 3、......
  • MYSQL优化
    PerformanceSchema程序插桩消费者表是一个经常受到批评的特性。早期版本的MySQL对其的实现不够理想，导致资源消耗较高。通常的建议是干脆关掉它。这也被认为是难以理解......
  • Windows Server 2012 R2上PHP、MySQL环境搭建
    一、准备工具服务器操作系统：WindowsServer2012PHP版本：5.6.9（根据自己需要）MySQL版本：MySQL8.0.17二、相关软件下载1、PHP下载​​​https://windows.php.net/downloads/relea......
  • kafka常用命令(zookeeper与bootstrap-server)
       在0.9.0.0之后的Kafka，出现了几个新变动，一个是在Server端增加了GroupCoordinator这个角色，另一个较大的变动是将topic的offset信息由之前存储在zookeeper......
  • SQLSERVER 居然也能调 C# 代码 ?
    一：背景1.讲故事前些天看到一个奇怪的Function函数，调用的是C#链接库中的一个UserLogin方法，参考代码如下：CREATEFUNCTIONdbo.clr_UserLogin(@name ASNVA......
  • 第十七章《MySQL数据库及SQL语言简介》第2节：MySQL数据库的下载、安装和配置
    ​MySQL数据库被广泛应用于各种行业软件，它开发了针对各种不同操作系统都开发了的版本。本节以Windows版本为例介绍MySQL数据库的下载、安装和配置。17.2.1MySQL的下载读者可......
  • pgsql触发器
    1、设置自动更新时间戳方法在使用finereport填报时，时间无法更新导致为空值，所以需要使用触发器，插入一条数据就插入时间。（1）定义触发器函数createorreplacefunctiontr......
  • Mysql为什么用B+树做索引而不用B-树或红黑树？
    一、概述B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域。所以从Mysql（Inoodb）的角度来看，B+树是用来充当索引的，一般来说索引非常大，尤其是关系性数......
  • 第十七章《MySQL数据库及SQL语言简介》第3节：数据库管理
    ​17.2小节主要讲解的是MySQL数据库的下载、配置和安装。从严格意义来讲，17.2小节所做的工作是对“数据库管理系统”进行下载、安装和配置。本小节所要讲解的数据库管理是指......
  • sqli-laba靶场搭建
    windows下安装sqli-laba环境:windows10安装phpstudy1.下载并安装小皮面板phpstudy（傻瓜式安装）https://www.xp.cn/windows-panel.html开启图中服务下载sql......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99