SQL Server 2000上的ROW_NUMBER() ?

I have a query that allows me to get records from a database table by giving it a minimum and maximum limit.

我有一个查询，它允许我通过给定一个最小和最大的限制从数据库表中获取记录。

It goes like this:

它是这样的:

  SELECT T1.CDUSUARIO, T1.DSALIAS, T1.DSNOMBRE_EMPRESA, T1.DSCARGO, T1.DSDIRECCION_CORREO, T1.CDUSUARIO_ADMINISTRADOR, T1.FEMODIFICACION 
    FROM (SELECT *, 
               ROW_NUMBER() OVER (ORDER BY CDUSUARIO) as row FROM TBL_USUARIOS ) as T1 
   WHERE row > @limiteInf 
     and row <= @limiteSup 
ORDER BY DSALIAS ASC;

Now, it works like heaven on SQL Server 2005 and SQL Server 2008 but tried to run it on an SQL Server 2000 database and says:

现在，它在SQL Server 2005和SQL Server 2008上就像天堂一样运行，但是尝试在一个SQL Server 2000数据库上运行它，并说:

ROW_NUMBER it's an unknown function name or something like that.

ROW_NUMBER是一个未知的函数名。

What can I do??

我能做什么? ?

4 个解决方案

#1

• There is a COUNT(*) with SELF JOIN solution here that will scale badly
• 这里有一个带有SELF JOIN解决方案的COUNT(*)，它的伸缩性很差
• You can load a temp table with an IDENTITY column and read back but it's not guaranteed to work (can't find article on it, was told at an MS Seminar years ago)
• 您可以加载一个带有标识列的临时表并读取它，但它不能保证工作(无法找到关于它的文章，这是几年前在MS研讨会上被告知的)

Neither solution will support PARTITION BY

两个解决方案都不支持分区。

I've not mentioned loop or CURSOR based solutions which are probably worse

我没有提到基于循环或基于游标的解决方案，这可能更糟糕

Edit 20 May 20011

编辑20011年5月20

Example demo of why IDENTITY won't work:
Do Inserted Records Always Receive Contiguous Identity Values

为什么标识不能工作的示例演示:插入记录总是接收连续的标识值

#2

Use another function or upgrade your database. ROW_NUMBER did not exist back in the 2000 version of the database. Point. Nothing you can do about it.

使用另一个函数或升级数据库。在2000版本的数据库中，ROW_NUMBER并不存在。点。对此你无能为力。

#3

I know this thread is bit old, but for anyone else looking for same solution, I think it will be useful to know that there is a good solution for this problem.

我知道这个线程有点旧，但是对于任何寻找相同解决方案的人来说，我认为知道这个问题有一个好的解决方案是有用的。

Please see the original link here

请在这里看到原始链接

For those who do not want to click on the link, I have copied and pasted the code below. Again, credit goes to original publisher

对于那些不想点击链接的人，我复制粘贴了下面的代码。同样，功劳归于原作者

Here is the below SQL for SQL Server 2000 to select the latest version of a record grouping by a single column.

下面是SQL Server 2000的SQL，用于选择由单个列分组的记录的最新版本。

SELECT *
  FROM (
    SELECT *, (
      SELECT COUNT(*)
        FROM MyTable AS counter
      WHERE counter.PartitionByColumn = MyTable.PartitionByColumn
        AND  counter.OrderByColumn >= MyTable.OrderByColumn
      ) AS rowNumber
    FROM MyTable
  ) AS r1
  WHERE r1.rowNumber = 1

Same code in SQL Server 2005 would look like this:

SQL Server 2005中的相同代码如下:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY PartitionByColumn 
    ORDER BY OrderByColumn DESC) AS rowNumber FROM MyTable) AS rw1 
  WHERE rw1.rowNumber = 1

#4

This is my solution to the problem:

这是我解决问题的办法:

declare @i int
declare @t table (row int, stuff varchar(99))
insert into @t
select 0,stuff from mytable -- <= your query
set @i=0
update @t set row=@i, @i=@i+1
select * from @t

Explanation:

解释:

1. create a memory table
2. 创建一个内存表
3. insert data (your query) with the row number as 0
4. 插入数据(您的查询)，行号为0。
5. update the row number field with an int variable which is incremented in the same update for the next record (actually the variable is incremented first and then updated, so it will start from 1)
6. 使用int变量更新行号字段，该变量在下一个记录的相同更新中递增(实际上，该变量首先递增，然后更新，因此它将从1开始)
7. "select" the result from the memory table.
8. 从内存表中“选择”结果。

You may ask, why don't i use the variable in the select statement? It would be simpler but it's not allowed, only if there is no result. It's ok to do it in an update.

你可能会问，为什么我不在select语句中使用变量呢?它会更简单，但不允许，只有在没有结果的情况下。在更新中完成它是可以的。