SQL Server 2000 上的 SQL Server ROW_NUMBER()? [英] SQL Server ROW_NUMBER() on SQL Server 2000?

问题描述

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

事情是这样的:

 SELECT T1.CDUSUARIO, T1.DSALIAS, T1.DSNOMBRE_EMPRESA, T1.DSCARGO, T1.DSDIRECCION_CORREO, T1.CDUSUARIO_ADMINISTRADOR, T1.FEMODIFICACION从(选择*，ROW_NUMBER() OVER (ORDER BY CDUSUARIO) 作为行 FROM TBL_USUARIOS ) 作为 T1WHERE 行 >@limiteInf和行 <= @limiteSup由 DSALIAS ASC 订购；

现在，它在 SQL Server 2005 和 SQL Server 2008 上运行得很好，但尝试在 SQL Server 2000 数据库上运行它并说:

<块引用>

ROW_NUMBER 它是一个未知的函数名称或类似的东西.

我能做什么??

解决方案

• 有一个带有 SELF JOIN 解决方案的 COUNT(*) 此处 会严重扩展
• 您可以加载一个带有 IDENTITY 列的临时表并回读，但不能保证它可以工作(找不到关于它的文章，多年前在 MS 研讨会上被告知)

两种解决方案都不支持 PARTITION BY

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

20011 年 5 月 20 日编辑

IDENTITY 为何不起作用的示例演示:
插入的记录是否始终接收连续的标识值>

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:

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

What can I do??

解决方案

• There is a COUNT(*) with SELF JOIN solution here that will scale badly
• 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)

Neither solution will support PARTITION BY

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

Edit 20 May 20011

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

这篇关于SQL Server 2000 上的 SQL Server ROW_NUMBER()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！