• SQL Server 存储过程分页



    每每面试,总会有公司问到分页。在下不才,在这里写几种分页,望路过的各位大神尽情拍砖。

    先从创建数据库说起。源码如下

    一.创建数据库

     1 /**********************************************************************
     2                         一.创建数据库DBTest
     3                        @author:    Alex Tian
     4                        Create Date:    2014-03-19
     5 ***********************************************************************/
     6 use master  --这里我们选择master数据库的目的是为了我们可以访问表
     7 --判断数据库清单中是否存在数据库DBTest
     8 if exists(select * from sysdatabases where name='DBTest')
     9 Drop DataBase DBTest   --删除数据库DBTest
    10 Go
    11 /*创建数据库的SQL语句,这里我们就创建DBTest数据库*/
    12 Create Database DBTest
    13 on primary  --默认就是primary文件组,可省略
    14 (
    15  /*--数据文件的具体描述--*/
    16       name='DBTest_data',  -- 主数据文件的逻辑名称
    17       filename='D:SQLDBTest_data.mdf', -- 主数据文件的物理名称
    18       size=5mb, --主数据文件的初始大小
    19       maxsize=100mb, -- 主数据文件增长的最大值
    20       filegrowth=15%--主数据文件的增长率
    21  )
    22  log on
    23  (
    24  /*--日志文件的具体描述,各参数含义同上--*/
    25      name='DBTest_log',
    26      filename='D:SQLDBTest_log.ldf',
    27      size=2mb,
    28      filegrowth=1mb
    29  )

    二.创建表

     1 /**********************************************************************
     2                         二.创建表Users
     3 ***********************************************************************/
     4 use DBTest   --选择我们刚刚创建的数据库DBTest
     5 Go
     6 if Exists (select * from sysobjects where name='Users')
     7 Drop Table Users
     8 go
     9 Create Table Users
    10 (
    11     ID int identity(1,1) primary key,  --表示是主键自增,标示种子是1.
    12     UName nvarchar(20) Not null,         --用户姓名不能为空
    13     USex  char(2)   --性别
    14 )

    三.插入数据

     1 /**********************************************************************
     2                         三.插入数据到表Users
     3 ***********************************************************************/
     4 insert into Users
     5 select 'yoyo',''
     6 union
     7 select 'Alex',''
     8 union 
     9 select '兰阳',''
    10 union
    11 select '彭伟',''
    12 union
    13 select '张琼',''
    14 union
    15 select '肖小仙',''
    16 union
    17 select '毛毛',''
    18 union
    19 select '田勇',''
    20 union
    21 select '田红',''
    22 union
    23 select '柯丽',''
    24 union
    25 select 'Gross',''
    26 union
    27 select '何军',''
    28 union
    29 select 'Leo',''
    30 union
    31 select '金琼',''
    32 union
    33 select '孙龙',''
    34 union
    35 select '老姚',''
    36 union
    37 select '李聪',''
    38 union
    39 select '王超',''
    40 union
    41 select '孙艳',''
    42 union
    43 select '曹瑞',''
    44 union
    45 select '王琼',''
    46 union
    47 select '沈炎',''
    48 union
    49 select '庄雪月',''
    50 union
    51 select '老丁',''
    52 union
    53 select '景天',''
    54 union
    55 select '雪见',''
    56 Go

       由于数据量太少,我这里重复插入了上面的测试数据,然后我们查询当前的表Users
     
       上面都是准备工作,废话少说。直接插入主题。

       1.下面我们用not in语句去分页,为了方便大家看,直接存储过程附上。

     1 select top 10 * from Users where (ID not in (select top 20 ID from Users order by ID asc) )
     2  order by ID
     3  
     4  create procedure sp_Page_View_with_not_in
     5  (
     6      @pageIndex int,--页索引。
     7      @PageSize int--页记录数
     8  )
     9  as
    10  begin
    11     set nocount on
    12     declare @strSQL varchar(1000)
    13     set @strSQL='(select top '+str(@PageSize)+' * from Users where (ID not In (select top '+str(@pageIndex*@PageSize)+' ID from Users order by ID asc)) order by ID)'    
    14     set nocount off
    15  end
    16  --drop procedure Page_View_with_not_in   --删除存储过程


    2.用Max分页,源码如下

     1 --2.使用select top 和select Max(列键)
     2 
     3 select top 10 * from Users where 
     4 ID>
     5  (
     6      select MAX(ID) from
     7      (
     8         select top 20  ID from Users order by ID
     9      ) as temp
    10  )
    11 order by ID
    12 
    13 --创建存储过程
    14  create procedure sp_Page_View_with_Max
    15  (
    16     @PageInde int, -- 当前页索引
    17     @PageSize int  --每页要显示的条数
    18  )
    19  as
    20  begin
    21     declare @strSQL nvarchar(1000)
    22     set @strSQL='select top '+str(@PageSize)+' * from Users where ID>(select MAX(ID) from (select top +'+str(@PageInde*@PageSize)+' ID from Users order by ID) as Temp )
    23     order by ID'
    24  end
    25  
    26 --drop procedure sp_Page_View_with_Max

    3.用ROW_NUMBER()分页(仅支持SQL Server 2005或2005之后的数据库),源码如下

     1 --3.利用Row_number()给数据行加索引(适用于,有很多数据表中没有identity ID的表)
     2 --假设我们当前的Users表没有identity
     3 select RID,UName,USex from
     4 (
     5 select *,ROW_NUMBER() over (order by ID) as RID 
     6 from Users
     7 )
     8 as tempTable
     9 where RID>20 and RID<=30
    10 
    11 create procedure sp_Page_View_With_ROW_NUMBER
    12 (
    13     @pageIndex int, 
    14     @pageSize int
    15 )
    16 as
    17 begin
    18     declare @strSQL nvarchar(1000)
    19     set @strSQL='select RID,UName,USex from (select *,ROW_NUMBER() over (order by ID asc) as RID from Users) as TempTable
    20 where RID>'+str(@pageIndex*@pageSize)+' and RID<='+str(@pageSize*(@pageIndex+1))+''    
    21 end


    以上是很常用的几种分页,当然还有很多种,比如 用临时表及Row_number ,互联网的大神们用select max方法用2分法等等等。
    这里由于数据量很小,我没有过多的去考虑性能。在这里我不对性能方面作于评价。仅用于面试之类的孩纸们了解一下。

  • 相关阅读:
    Dos命令快速设置ip、网关、dns地址
    远程桌面连接保存登陆用户以及密码(凭据)备份方法
    如何启用windows8, windows10中被停用的远程桌面,如何连接windows10远程桌面?
    通过日志恢复SQL Server的历史数据
    http://sourceforge.net/projects/rtspdirectshow/
    iphone上实现H264 硬编码
    利用lipo编译合并iPhone模拟器和真机通用的静态类
    在iOS上使用ffmpeg播放视频
    基于.Net的单点登录(SSO)解决方案
    java实现简单的单点登录
  • 原文地址:https://www.cnblogs.com/tianchangjian/p/3611921.html
Copyright © 2020-2023  润新知