如何在具有数据的表上创建序号列索引 [英] How to create sequential number column index on table with data

问题描述

我有下表，其中包含10个唯一的行，BookingID是包含随机数的FK.该数字不必按顺序.

I have the following table with 10 unique rows, BookingID is a FK containing a random number. The number doesn't need to be in sequence.

BookingID, Description
1000       Foo
3000       Bar
1500       Zoo

我需要插入一个从1..x开始的名为ID的顺序索引

I need to insert an sequential index called ID which goes from 1..x

如何在SQL Server 2005中做到这一点?我当时想写一个游标，x = x + 1，但是也许有更好的方法吗?

how do I do that in SQL Server 2005? I was thinking to write a cursor and x=x+1 but maybe there is a better way?

这是我想要的结果

Id, BookingID, Description
1   1000       Foo
2   3000       Bar
3   1500       Zoo

推荐答案

此:

SELECT ROW_NUMBER() OVER(ORDER BY t.bookingid) AS id,
       t.bookingid,
       t.description
  FROM YOUR_TABLE t

...将产生:

id  bookingid   description
----------------------------
1   1000        Foo
2   3000        Bar
3   1500        Zoo

要更新现有的id列，请使用:

To update the existing id column, use:

WITH cte AS (
   SELECT t.id,
          ROW_NUMBER() OVER(ORDER BY t.bookingid) AS rank,
          t.bookingid,
          t.description
     FROM YOUR_TABLE t)
UPDATE cte
   SET id = rank

这篇关于如何在具有数据的表上创建序号列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！