分页查询（二）——用sql与linq查询

缺乏、安全感 2022-08-23 03:54 218阅读 0赞

以前只知道分页，但是不知道如何实现的，曾经有次面试让我实现分页，搜了很久，只是知道有个top，但是不懂得原理，没有搞出来。今天在学习link to ef时，使用到了一条语句就把分页搞定了，然后我才明白了原理。

1，下面是link to ef中使用到的分页方法：

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

//创建数据库实体
                using (studentEntities stuEntity = new studentEntities())
                {
                    int skipPage=2;//要跳过多少页，指定2也就是跳到第3页
    
                    int countPerPage = 4;//分页大小(每一页有多少条数据)
    
                    var result = stuEntity.T_StuInfo //这是使用ef生成的
                        .OrderBy(t => t.stuid) //分页必须排序
                        .Skip<T_StuInfo>(skipPage * countPerPage) //跳过制定页数
                        .Take<T_StuInfo>(countPerPage);//获取制定条数
                    Console.WriteLine("第{0}页:", skipPage + 1);
                    //输出查询结果(该页)
                    foreach (var item in result)
                    {
                        Console.WriteLine("id:{0},name:{1},phone:{2},classid:{3}", item.stuid, item.name, item.phone, item.classid);
    
                    }
                    Console.WriteLine("OK");
                    Console.Read();
                }

![复制代码][copycode.gif]

原理：按照某个字段(如stuid)排序后，跳过(使用skip)前面的分页(实际跳过数据=分页大小\*跳过的页数),得到后面的数据后再取出来(使用take)前面分页大小(一页有多少条数据),这样就得到了所求分页的数据。

2，下面这条语句实现分页

![复制代码][copycode.gif]

select top 4 *   --分页大小（每页有多少条数据）
    from 
    (
    select row_number() over (order by stuid asc) as RowNumber,* from T_StuInfo
    )as temp
    where RowNumber > 4*(2-1)

![复制代码][copycode.gif]

对应的公式是：

![复制代码][copycode.gif]

select top pagesize *   --分页大小（每页有多少条数据）
    from 
    (
    select row_number() over (order by 某字段 asc) as RowNumber,* from 表名
    )as temp --临时变量
    where RowNumber > pagesize*(pages-1) --(pages-1)为跳过多少页

![复制代码][copycode.gif]

分析：

(1)首先是select top 4 \* from 表名

它的意思是从表中取出前4条数据。

(2)select name as 姓名,phone as 电话 from T\_StuInfo

这里面as相当于给字段起一个别名，这样得到的结果就是用"姓名"来代替"name"

(3)row\_number() over (order by 某字段 asc) as RowNumber

这里面就给前面的row\_number()起了一个别名RowNumber,然后下面就可以使用这个别名；

row\_number() 是sql2005中增添的一个函数，用于表示行号，和over搭配使用，over后面指定

按照哪个字段升序或降序排列。

(4)( select row\_number() over (order by 某字段 asc) as RowNumber,\* from 表名 )as temp --临时变量

这个相当于给得到的表起了一个别名temp,这个就是临时表的名字。

(5)假如把排序那行改成一个临时的表，这样就非常清楚了。

select top pagesize *   --分页大小（每页有多少条数据）
    from  temp --临时表名
    where RowNumber > pagesize*(pages-1) --(pages-1)为跳过多少页

3，下面一个存储过程可以实现千万条记录级别的分页。

(1)

![复制代码][copycode.gif]

CREATE  PROCEDURE pGo_GetRecordFromPage
        @tblName      varchar(255),       -- 表名
        @fldName      varchar(255),       -- 字段名
        @PageSize     int = 10,           -- 页尺寸
        @PageIndex    int = 1,            -- 页码
        @IsCount      bit = 0,            -- 返回记录总数, 非 0 值则返回
        @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序
        @strWhere     varchar(1000) = ''  -- 查询条件 (注意: 不要加 where)
    AS declare @strSQL   varchar(6000)       -- 主语句
    declare @strTmp   varchar(500)        -- 临时变量
    declare @strOrder varchar(400)        -- 排序类型
    if @IsCount != 0                         -- 假如是查询记录总数，直接应用Count(0)函数
     begin
      if @strWhere != ''
       set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
      else
       set @strSQL = 'select count(*) as Total from [' + @tblName + '] '
     end
    --假如是想查询记载，则
    else
     begin
      if @PageIndex = 1        --如果是第一页
       begin
            set @strTmp = ''
            if @strWhere != ''
                 set @strTmp = ' where ' + @strWhere         set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
                 + @tblName + ']' + @strTmp + ' ' + @strOrder
       end
    
      else                    --如果不是第一页
       begin
        --假如是降序查询……
        if @OrderType != 0
         begin
              set @strTmp = '<(select min'
              set @strOrder = ' order by [' + @fldName +'] desc'
         end
        --如果是升序查询……
        else
         begin
              set @strTmp = '>(select max'
              set @strOrder = ' order by [' + @fldName +'] asc'
         end    
        if @strWhere != ''
             set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
                  + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
                  + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
                  + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
                  + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
        else
         set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
              + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
              + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
              + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
              + @strOrder   
       end
    end exec (@strSQL)
    GO

![复制代码][copycode.gif]

(2)下面是在sql查询语句中执行存储过程

[ExpandedBlockStart.gif]: /images/20220722/44f3bb2987d04296a452ae069cc01b66.png
[copycode.gif]: /images/20220722/8e7e67020b3944b690a50475b5ca862d.png