在 sql server2000 中怎么实现ROW_NUMBER() 函数
日期：2014-05-20 浏览次数：20873 次
在 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
------解决方案--------------------
问题如下：
备注：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
* */
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
