SQL Server 2000 上的 SQL Server ROW_NUMBER()？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4081753/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server ROW_NUMBER() on SQL Server 2000?
提问by gbn
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??
我能做什么？？
回答by gbn
- There is a COUNT(*) with SELF JOIN solution herethat 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)
- 有一个COUNT（*）与自连接解决方案在这里，将扩展严重
- 您可以加载一个带有 IDENTITY 列的临时表并回读，但它不能保证工作（找不到关于它的文章，多年前在 MS 研讨会上被告知）
Neither solution will support PARTITION BY
两种解决方案都不支持 PARTITION BY
I've not mentioned loop or CURSOR based solutions which are probably worse
我没有提到基于循环或 CURSOR 的解决方案，它们可能更糟
Edit 20 May 20011
20011 年 5 月 20 日编辑
Example demo of why IDENTITY won't work:
Do Inserted Records Always Receive Contiguous Identity Values
IDENTITY 为何不起作用的示例演示：
Do Inserted Records Always Receive Contiguous Identity Values
回答by Dilaksha A
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
回答by TomTom
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.
使用其他功能或升级您的数据库。ROW_NUMBER 在 2000 版本的数据库中不存在。观点。你对此无能为力。
回答by Kastal András
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:
解释：
- create a memory table
- insert data (your query) with the row number as 0
- 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)
- "select" the result from the memory table.
- 创建内存表
- 插入行号为 0 的数据（您的查询）
- 使用 int 变量更新行号字段，该变量在下一条记录的同一更新中递增（实际上该变量先递增然后更新，因此它将从 1 开始）
- 从内存表中“选择”结果。
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 语句中使用变量？这会更简单，但不允许，只有在没有结果的情况下。可以在更新中做到这一点。
