在 sql server2000 中怎么实现ROW_NUMBER() 函数-C#教程-爱易网页

在 sql server2000 中怎么实现ROW_NUMBER() 函数

日期：2014-05-20　浏览次数：20825 次

在 sql server2000 中如何实现ROW_NUMBER() 函数，在线等
问题如下：
备注：page 是页数
在sql server2005中可以使用ROW_NUMBER() OVER 达到分页数据量大约110万行左右

select * from( select ROW_NUMBER() OVER( ORDER BY r.id ) AS RowNumber,c.cha_name,RTRIM(content) as content from dbo.Resource r inner join dbo.character c on r.cha_id = c.cha_id) T where RowNumber between (" + page + "-1)*100+1 and " + page + "*100 order by 1

请问在sql server2000 中如何实现(万不得已，请不要使用 top，除非性能高。谢谢) 没有分了，抱歉。下次补。

------解决方案--------------------
select identity(int,1,1) Row, *
into #studentInfo
from studentInfo
order by id desc

参考http://www.cnblogs.com/gaolonglong/archive/2010/09/24/1834207.html
------解决方案--------------------

C# code

        /*
     * 
     * CREATE  PROCEDURE [dbo].[ProcCustomPage]
        (
            @Table_Name               varchar(5000),              --表名
            @Sign_Record              varchar(50),               --主键
            @Filter_Condition         varchar(1000),             --筛选条件,不带where
            @Page_Size                int,                       --页大小
            @Page_Index               int,                      --页索引                 
            @TaxisField               varchar(1000),            --排序字段
            @Taxis_Sign               int,                       --排序方式 1为 DESC, 0为 ASC
            @Find_RecordList          varchar(1000),            --查找的字段
            @Record_Count             int                        --总记录数
         )
         AS
            BEGIN 
            DECLARE  @Start_Number          int
            DECLARE  @End_Number            int
            DECLARE  @TopN_Number           int
         DECLARE  @sSQL                  varchar(8000)
                 if(@Find_RecordList='')
                 BEGIN
                      SELECT @Find_RecordList='*'
                 END
         SELECT @Start_Number =(@Page_Index-1) * @Page_Size
            IF @Start_Number<=0
         SElECT @Start_Number=0
            SELECT @End_Number=@Start_Number+@Page_Size
            IF @End_Number>@Record_Count
         SELECT @End_Number=@Record_Count
         SELECT @TopN_Number=@End_Number-@Start_Number
         IF @TopN_Number<=0
         SELECT @TopN_Number=0
            print @TopN_Number
         print @Start_Number
         print @End_Number
         print @Record_Count
                 IF @TaxisField=''
                 begin
                    select  @TaxisField=@Sign_Record
                 end
         IF @Taxis_Sign=0
              BEGIN
                 IF @Filter_Condition=''
                 BEGIN
                     SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
                         WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
                         WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
                     ORDER BY '+@TaxisField+') order by '+@TaxisField+' DESC)order by '+@TaxisField
                 END
                ELSE
                BEGIN
                SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
             WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
             WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
             WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField
                 END
            END
        ELSE
            BEGIN
            IF @Filter_Condition=''
                BEGIN
                    SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
                 WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
                 WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
                 ORDER BY '+@TaxisField+' DESC) order by '+@TaxisField+')order by '+@TaxisField+' DESC'
             END
            ELSE
            BEGIN
                SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
             WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
             WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
             WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC'
         END
            END
            EXEC (@sSQL)
            IF @@ERROR<>0
            RETURN -3              
         RETURN 0
         END
         
         PRINT  @sSQL
        GO

     * */

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

在 sql server2000 中怎么实现ROW_NUMBER() 函数

相关资料更多>

推荐阅读更多>