更新列问题

[jamk]

小弟不才 有以下两列：
lie1 lie2
A 0
A 0
A 0
B 0
B 0
B 0
B 0
C 0
C 0
D 0
D 0
D 0
E 0
如何更新成：
lie1 lie2
A 0
A 1
A 2
B 0
B 1
B 2
B 3
C 0
C 1
D 0
D 1
D 2
E 0


谢谢！各位！

[---涛声依旧---]

是要更新啊，还是只是仅仅查询结果是那样啊？

[lzw_0736]

declare @table table (ie1 char(1),lie2 int) insert into @table select 'A',0 union all select 'A',0 union all select 'A',0 union all select 'B',0 union all select 'B',0 union all select 'B',0 union all select 'B',0 union all select 'C',0 union all select 'C',0 union all select 'D',0 union all select 'D',0 union all select 'D',0 union all select 'E',0 ; with a as ( select *,ROW_NUMBER() OVER(PARTITION BY ie1 order by lie2) id from @table ) update a set lie2=id-1 select * from @table

[微wx笑]

尝试了一下用一条语句解决此问题，但没能成功。

[IEEE_China]

if object_id('Tempdb..#t') is not null drop table #t
create table #t(
id int identity(1,1) not null,
[lie1] nvarchar(100) null,
[lie2] int null
)
Insert Into #t
select 'A',0 union all
select 'A',0 union all
select 'A',0 union all
select 'B',0 union all
select 'B',0 union all
select 'B',0 union all
select 'B',0 union all
select 'C',0 union all
select 'C',0 union all
select 'D',0 union all
select 'D',0 union all
select 'D',0 union all
select 'E',0 


-----更新
 update #t set lie2=t.col-1 from (select id tid,ROW_NUMBER() OVER(PARTITION BY lie1 order by lie2) col from #t ) t where id=t.tid


---查询
select * from #t

---结果
id          lie1                                                                                                 lie2
----------- ---------------------------------------------------------------------------------------------------- -----------
1           A                                                                                                    0
2           A                                                                                                    1
3           A                                                                                                    2
4           B                                                                                                    0
5           B                                                                                                    1
6           B                                                                                                    2
7           B                                                                                                    3
8           C                                                                                                    0
9           C                                                                                                    1
10          D                                                                                                    0
11          D                                                                                                    1
12          D                                                                                                    2
13          E                                                                                                    0

(13 行受影响)

[tcmakebest]

这个要求相当变态，尤其是两列的内容完全一样，实际应用应该避免这种情况，设置主键后就不会产生这样的数据了。 下面更新语句，请把 @t 换成你的表名

while 1=1
begin
	update top (1) t set lie2=t2.num+1
	from @t t
	join (select lie1,MAX(lie2) num from @t group by lie1) t2 on t.lie1=t2.lie1
	join (select lie1,COUNT(*)  cnt from @t where lie2=0 group by lie1) t3 on t.lie1=t3.lie1 and t3.cnt>1
	where t.lie2=0
	if @@ROWCOUNT=0 break
end