消除 SQL 2000 的 ROW_NUMBER()

发布于 2024-11-02 03:03:51 字数 272 浏览 2 评论 0原文

我必须迁移一个 sql 才能在 Microsoft SQL Server 2000 上工作。不幸的是，当前的 sql 使用该版本尚不支持的函数 ROW_NUMBER()。因此我必须找到类似的东西。

下面是我的 SQL（我使用 * 而不是列出所有列）

SELECT [Id], ROW_NUMBER() OVER (ORDER BY InstallmentNumber, ID ASC) AS ROWID
FROM [ARAS].[ARAS].[Movement]

原文

I have to migrate a sql to work on Microsoft SQL Server 2000. Unfortunately the current sql uses the function ROW_NUMBER() which is not yet supported in this version. Therefore I have to find something similar.

Below my SQL (I used * instead of listing all columns)

SELECT [Id], ROW_NUMBER() OVER (ORDER BY InstallmentNumber, ID ASC) AS ROWID
FROM [ARAS].[ARAS].[Movement]

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半城柳色半声笛 2024-11-09 03:03:51

使用带有标识列的临时表来模拟 ROW_NUMBER 可能是性能方面的最佳选择：

CREATE TABLE #tmpRowNum (
    ROWID INT IDENTITY(1,1),
    ID INT
)

INSERT INTO #tmpRowNum
    (ID)
    SELECT ID
        FROM [ARAS].[ARAS].[Movement]
        ORDER BY InstallmentNumber, ID

Using a temp table with an identity column to simulate the ROW_NUMBER may be your best bet performance wise:

CREATE TABLE #tmpRowNum (
    ROWID INT IDENTITY(1,1),
    ID INT
)

INSERT INTO #tmpRowNum
    (ID)
    SELECT ID
        FROM [ARAS].[ARAS].[Movement]
        ORDER BY InstallmentNumber, ID

回复收藏 0 原文

独木成林 2024-11-09 03:03:51

不太确定，但这是一个起点：

SELECT [Id],
  ( SELECT SUM(1)
    FROM [ARAS].[ARAS].[Movement]
    WHERE InstallmentNumber <= OuterMovement.InstallmentNumber
      AND ID <= OuterMovement.ID
  ) AS ‘Row Number’
FROM [ARAS].[ARAS].[Movement] AS OuterMovement

Not too sure, but here's a starting-point:

SELECT [Id],
  ( SELECT SUM(1)
    FROM [ARAS].[ARAS].[Movement]
    WHERE InstallmentNumber <= OuterMovement.InstallmentNumber
      AND ID <= OuterMovement.ID
  ) AS ‘Row Number’
FROM [ARAS].[ARAS].[Movement] AS OuterMovement

回复收藏 0 原文

笔芯 2024-11-09 03:03:51

您可以创建一个变量表，为“ROWID”提供 INT Identity 值并将这些值插入其中。

您也可以在不使用 ROWID 的情况下执行此操作。不过表演不会太好...

DECLARE @id INT, @SUM INT
SELECT @id = MIN([Id]) FROM [ARAS].[ARAS].[Movement]    


WHILE EXISTS (SELECT [Id] FROM [ARAS].[ARAS].[Movement] WHERE [Id] >= @id)
BEGIN
    ... do something with the ID ..
    SELECT @SUM = SUM(...) FROM [ARAS].[ARAS].[Movement] WHERE [Id] > @id

    SELECT @id = MIN([Id]) FROM [ARAS].[ARAS].[Movement] WHERE [Id] > @id
END

You can make a variable table giving the "ROWID" an INT Identity value and inserting the values into that.

You can also do while w/o using the ROWID. The performance wouldn't be great though...

DECLARE @id INT, @SUM INT
SELECT @id = MIN([Id]) FROM [ARAS].[ARAS].[Movement]    


WHILE EXISTS (SELECT [Id] FROM [ARAS].[ARAS].[Movement] WHERE [Id] >= @id)
BEGIN
    ... do something with the ID ..
    SELECT @SUM = SUM(...) FROM [ARAS].[ARAS].[Movement] WHERE [Id] > @id

    SELECT @id = MIN([Id]) FROM [ARAS].[ARAS].[Movement] WHERE [Id] > @id
END

回复收藏 0 原文

~没有更多了~