如何在SQL Server 2012中添加行号列

I'm trying to add a new column to an existing table, where the value is the row number/rank. I need a way to generate the row number/rank value, and I also need to limit the rows affected--in this case, the presence of a substring within a string.

我试图向现有表添加一个新列，其中的值是行号/秩。我需要一种生成行号/rank值的方法，我还需要限制受影响的行——在这种情况下，字符串中存在子字符串。

Right now I have:

现在我有:

UPDATE table
SET row_id=ROW_NUMBER() OVER (ORDER BY col1 desc) FROM table
WHERE CHARINDEX('2009',col2) > 0

And I get this error:

我得到了这个错误

Windowed functions can only appear in the SELECT or ORDER BY clauses.

(Same error for RANK())

(同样的错误等级())

Is there any way to create/update a column with the ROW_NUMBER() function? FYI, this is meant to replace an incorrect, already-existing "rank" column.

是否有方法使用ROW_NUMBER()函数创建/更新列?顺便说一句，这是为了替换一个错误的、已经存在的“rank”列。

3 个解决方案

#1

You can do this with a CTE, something like:

你可以用CTE来做，比如:

with cte as
(
  select *
    , new_row_id=ROW_NUMBER() OVER (ORDER BY col1 desc)
  from MyTable
  where charindex('2009',col2) > 0
)
update cte
set row_id = new_row_id

SQL Fiddle with demo.

SQL摆弄演示。

#2

If you are only updating a few thousand rows, you could try something like this:

如果您只更新几千行，您可以尝试以下操作:

select 'UPDATE MyTable SET ID = ' + CAST(RowID as varchar) + ' WHERE ID = ' + CAST(ID as varchar)
From (
select MyTable, ROW_NUMBER() OVER (ORDER BY SortColumn) RowID from RaceEntry 
where SomeClause and SomeOtherClause
) tbl

Copy and paste the query results into the query editor and run. It's a bit sluggish and yukky bit it works.

将查询结果复制并粘贴到查询编辑器中并运行。它有点迟缓，而且它还能工作。

#3

-1

Simple workaround would be to create a temp table that looks like

简单的解决方法是创建一个看起来像的临时表。

CREATE TABLE #temp (id int, rank int)

创建表#temp (id int, rank int)

Where id is the same type as primary key in you main table.

在主表中，id与主键的类型相同。

Just use SELECT INTO to first fill temp table and then update from temp table…

只需使用SELECT INTO首先填充临时表，然后从临时表中更新……

#1