如何使用ROW_NUMBER（）OVER值更新列值，该值基于列的不同值 [英] how to update a column value with ROW_NUMBER() OVER value based on distinct values of a column

问题描述

我有一个表，我想在其中更新特定列的值。列值应为行号。根据列的不同值，我需要更新新创建的列值。



示例：

表名 - INFO_T



名称| id

------------

Arijit | 120

Suman | 121

Priyam | 122

Arijit | 123

Suvayu | 124

Priyam | 125



我需要在上表INFO_T中再添加一列，下面是示例。我必须使用ROW_NUMBER（）OVER函数，通过它我必须更新那个SEQ_NO列。



名称| id | Seq_no

------------------

Arijit | 120 |

Suman | 121 |

Priyam | 122 |

Arijit | 123 |

Suvayu | 124 |

Priyam | 125 |



更新表后，它应如下所示 -



名称| id | Seq_no

------------------

Arijit | 120 | 1

Suman | 121 | 2

Priyam | 122 | 3

Arijit | 123 | 1

Suvayu | 124 | 4

Priyam | 125 | 3



我无法得到上述结果。请帮我。



高级谢谢。

I have one table, in which I want to update the value for a particular column. The column value should be row number. Based on a column distinct value I need to update the newly created column value.

Example:
Table name - INFO_T

Name | id
------------
Arijit | 120
Suman | 121
Priyam | 122
Arijit | 123
Suvayu | 124
Priyam | 125

I need to add one more column within the above table INFO_T, below is the example. I have to use ROW_NUMBER() OVER function, through which I have to update that SEQ_NO column.

Name | id | Seq_no
------------------
Arijit | 120 |
Suman | 121 |
Priyam | 122 |
Arijit | 123 |
Suvayu | 124 |
Priyam | 125 |

After UPDATE the table, it should be like below -

Name | id | Seq_no
------------------
Arijit | 120 | 1
Suman | 121 | 2
Priyam | 122 | 3
Arijit | 123 | 1
Suvayu | 124 | 4
Priyam | 125 | 3

I am unable to get that above result. Please help me.

Advanced thanks.

推荐答案

不确定我是否仍然正确理解了这个问题，但确实如此查询返回正确的结果？

Not sure if I still understood the question correctly, but does this kind of query return the correct results?

create table test21  (
   name varchar(100),
   id   int
);

insert into test21 values ('Arijit', 120);
insert into test21 values ('Suman', 121);
insert into test21 values ('Priyam', 122);
insert into test21 values ('Arijit', 123);
insert into test21 values ('Suvayu', 124);
insert into test21 values ('Priyam', 125);

select t1.name,
       t1.id,
	   t2.seq_no
from test21 t1,
     (select t3.name,  row_number() over (order by t3.name) as seq_no
	  from (select distinct name 
	        from test21) t3
	  ) t2
where t2.name = t1.name
order by t1.id;

这是一个查询，以按照您指定的顺序获取结果。

并且还更新变量表@INFO_T with期望的结果。

从CTE格式，你应该能够查看数据是如何被操纵的。

Here is a query to get the results in the order you have specified.
And also update the variable table @INFO_T with the desired result.
From the CTE format, you should be able to see how the data is being manipulated.

--setup test data
declare @INFO_T table (Id int, Name varchar(100), Seq_no int);
insert into @INFO_T (Name, Id)
	select 
		'Arijit' Name,
		120 Id
	union select 'Suman', 121
	union select 'Priyam', 122
	union select 'Arijit', 123
	union select 'Suvayu', 124
	union select 'Priyam', 125
;

--update OrigData with FinalData
with GroupNames as ( --Group the Names
	select 
	Name,
	Id,
	row_number() over (partition by name order by id) as rowid
	from @INFO_T OrigData
), NameSequence as ( --Get Squence of Names via Id
	select 
		Name,
		row_number() over (order by id) as Seq_No
	from GroupNames
	where rowid = 1
)
update FinalData
set FinalData.Seq_No = NameSequence.Seq_No
from @INFO_T OrigData
inner join NameSequence
	on OrigData.name = NameSequence.name
inner join @INFO_T FinalData
	on OrigData.Id = FinalData.Id
;

--final result
select Name, Id, Seq_no 
from @INFO_T 
order by Id;

希望有所帮助。

Hope that helps out.

这篇关于如何使用ROW_NUMBER（）OVER值更新列值，该值基于列的不同值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！