小弟不才 有以下两列:
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
谢谢!各位!C
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
谢谢!各位!C
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-1select * from @table
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 行受影响)
下面更新语句,请把 @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