• sql语句分页多种方式


    sql语句分页多种方式ROW_NUMBER()OVER

    sql语句分页多种方式ROW_NUMBER()OVER
    2009年12月04日 星期五 14:36

    方式一

    select top @pageSize * from company where id not in

    (select top @pageSize*(@pageIndex-1) id from company)

     

    方式二ROW_NUMBER()OVER

     

    --ROW_NUMBER() 就是生成一个有顺序的行号,而他生成顺序的标准,就是后面紧跟的OVER(ORDER BY ID)

    --还必须添加OVER语句以便告诉SQL Server你希望怎样添加行序号。

    select getdate()

    select * from company where id in (

    --搜索出settable表中所有的编号,也就是company表中的id,这里只不过要得到num(即有顺序的编号)

    select id from

    --搜索出出表中的所有的id,并且新建一列num用来存取排序的编号,并且把这张表赋值给settable

    (select id,row_number() over (order by id) as

    num from company)

    as settable

    --添加搜索条件页索引和页大小

    where num between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize)

    select getdate()

     

    方式三

    SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY id asc) AS rownum,

    id

    FROM company ) AS D

    WHERE rownum BETWEEN (@pageIndex-1)*@pageSize+1 AND @pageIndex*@pageSize

    ORDER BY id asc

     

    Sql Server 2000的自定义分页,但是在sql server 2000中,要实现显示某一页,就返回那一页数据的效果的方法实在不尽人意.网上很多通用的分页存储过程,但看着就头大.如果使用我前面提到的使用in,not in,top来进行返回特定页,特殊的限制又会比较多(比如ID要递增).现在Sql Server 2005中提供了一个函数ROW_NUMBER(),可以使自定义分页变得简单许多.      我们先来看看ROW_NUMBER()是干什么的.执行下面这段SQL语句:      SELECT [ReportID],[UserName], [ReportID],      [TimeStart], [TimeEnd],ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo      FROM [ExecutionLog] 很简单,ROW_NUMBER() 就是生成一个顺序的行号,而他生成顺序的标准,就是后面紧跟的OVER(ORDER BY ReportID).现在,你看到了自定义分页的影子了吗?:)下面,我们看看怎么具体应用这个RowNo进行分页.      现在,假设我每一页的数据是10条,我们就可以使用如下所示的SQL语句返回指定页的数据:      @"        SELECT TOP 10 *        FROM        (         SELECT top 10 [InstanceName], [UserName], [ReportID],         [TimeStart], [TimeEnd],ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo         FROM [ExecutionLog]        ) AS A        WHERE RowNo > " + pageIndex*10      pageIndex就是我们需要数据的页数.很简单,不是吗?并且,这种方式几乎没有什么限制,因为他相当于对于任何检索,都生成了一个新的排序列.我们就可以使用该列进行自定义分页.

    ================

    下面举个例子:

    ROW_NUMBER函数

      SQL Server2005为我们引入了一个ROW_NUMBER函数。你是否曾经需要为你的查询结果集做行序号?你有时会发现能够为行做序号是一件很有用的事情。从前,你不得不作棘手的事,像创建一个有序号列的临时表,然后把你的SELECT结果插入到这个临时表中。现在,用ROW_NUMBER函数,你就可以获得添加在你的结果集的增加列中的行序号。为了获得行序号,你只要简单的将ROW_NUMBER函数作为一列添加进你的SELECT语句中。你还必须添加OVER语句以便告诉SQL Server你希望怎样添加行序号。

      SELECT ROW_NUMBER() OVER(ORDER BY employee_id) AS 'Row Number', * from   dbo.employee

    结果

    Row Number employee_id Firstname Lastname soc_sec 1 5623222 Tim Jones 123-65-8745 2 5632111 Rob Kinkad 456-69-8754 3 6365666 Jim Miller 236-56-8989 4 7563333 Joe Roberts 564-89-5555

      这个查询返回所有的雇员和一个显示每条记录在哪一行的一个序号。OVER语句使SQL Server基于employee_id列增加行序号。换句话说,产生了行序号,就好像数据按employee_id做了排序。这是很重要的一点,因为你仍然可以改变SELECT的排序顺序。以下面的查询为例:

      SELECT ROW_NUMBER() OVER(ORDER BY employee_id) AS 'Row Number', * from   dbo.employee   ORDER BY soc_sec

    结果

    Row Number employee_id Firstname Lastname soc_sec 1 5623222 Tim Jones 123-65-8745 3 6365666 Jim Miller 236-56-8989 2 5632111 Rob Kinkad 456-69-8754 4 7563333 Joe Roberts 564-89-5555

      注意第二个结果集数据是按社会安全编号来排序的,但是行号仍然创建得好像数据是按employee_id排序的。

    -------------------------------分页存储过程

    IF EXISTS (SELECT * FROM sysobjects where name='P_student') DROP PROCEDURE P_student go CREATE PROCEDURE P_student @startIndex INT,     @pageSize INT AS begin WITH studentList AS ( SELECT ROW_NUMBER() OVER (ORDER BY O.stuid ) Row, O.stuid,O.stuname,O.stuage,O.stuinfo from student O)

    SELECT Row, stuid,stuname,stuage,stuinfo FROM studentList WHERE Row between @startIndex and @startIndex+@pageSize-1 end

     

     

    -------------------分页2---------

    set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Deer_Page] ( @startIndex INT ,@pageSize INT ,@strSql varchar(5000) ---查询条件 ,@TableName varchar(50) ,@DoCount AS bit=1     -- 0值返回记录总数, 非 0 值则返回记录 ) AS begin tran IF @DoCount=0     Goto GetCount Else     Goto GetSearch

    GetCount: --返回记录总数     DECLARE @SearchSql AS Nvarchar(4000)     SET @SearchSql= 'SELECT Count(*) AS Total FROM '+@TableName+' WHERE IntReserve1=0'     exec sp_executesql @SearchSql     --print @SearchSql COMMIT TRAN     return

    GetSearch: --返回记录 DECLARE @SqlQuery varchar(4000) SET @SqlQuery='SELECT * FROM            (SELECT ROW_NUMBER() OVER (ORDER BY O.ID ) Row, * from '+@TableName+' O Where IntReserve1=0) as temp             WHERE Row BETWEEN '+cast(@startIndex as varchar) +' and '+cast(@startIndex+@pageSize-1 as varchar)+ @strsql ---print @SqlQuery execute(@SqlQuery) COMMIT TRAN

  • 相关阅读:
    铁老大:不管你信不信,我是信了的NET代码版
    如何去掉字符串中的空格(转)
    DNN 社交挂件模块和DNN天气模块
    top、postop、scrolltop、scrollHeight、offsetHeight详解以及各浏览器显示效果差异
    vs2008视图菜单栏没有工具箱的解决办法
    DNN资源收集
    LINQ如何实现模糊查询
    Linq to excel
    前端各种出色的弹出层
    repo init 中指定manifest和branch的含义
  • 原文地址:https://www.cnblogs.com/nxxshxf/p/5614181.html
Copyright © 2020-2023  润新知