如何根据列的不同值使用ROW_NUMBER()OVER值更新列值

您可以使用 dense_rank() 为此目的:

select name, id, dense_rank() over (order by minid) as seqno
from (select t.*, min(id) over (partition by name) as minid
      from table t
     ) t;

如果你想这样做 row_number() :

select t.name, t.id, tt.seqnum
from table t join
     (select t.name, row_number() over (order by min(id)) as seqno
      from table t
      group by t.name
     ) tt
     on t.name = tt.name;

然而,我不知道你为什么要这样做。

SQL FIDDLE DEMO

SELECT Table1.name, Table1.ID, SEQ.Seq_no
FROM 
    (SELECT name, ROW_NUMBER() OVER(order by name) as Seq_no
     FROM (SELECT  DISTINCT name FROM Table1) as unique_name ) as SEQ INNER JOIN
     Table1 on SEQ.name = Table1.Name

这是MS SQL,但看起来Row_Number在Oracle中的功能相同。如果不能执行CTE(WITH)语句,则可以将其作为子查询来执行。也许有Oracle SQL经验的人可以使用此示例并重新编写。我不能保证结果,因为它们是无序的,但基本上我首先从表中创建一个RowID,没有顺序。这使我能够确保名称的顺序与您的顺序相同(这似乎是插入顺序)。然后我基本上进行了子查询,以按顺序获得每个名称的第一个不同实例。然后我又回到了原来的桌子上。在这里 SQLFiddle .

;WITH NoOrderBy AS
(
  select Row_Number() OVER(ORDER BY (SELECT 0)) RowID,Name,id
  FROM MyTable
),
DistinctNames  AS
(
  SELECT Row_Number() OVER(ORDER BY RowID) RowID3,Name,RowID,id
  FROM
  (
    SELECT Row_Number() OVER(PARTITION BY NAME ORDER BY RowID) RowID2,Name,RowID,id
    FROM NoOrderBy
   ) a
   WHERE RowID2 = 1
)
SELECT mt.Name,di.id,di.RowID3 Seq_no 
FROM MyTable mt
INNER JOIN DistinctNames di
ON mt.Name =di.Name