27,579
社区成员
发帖
与我相关
我的任务
分享
create table 表C(列A int,列B int)
insert into 表C(列A)
select 1 union all
select 2 union all
select 3 union all
select 3 union all
select 3 union all
select 4 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 8 union all
select 8 union all
select 8 union all
select 9
update a
set a.列B=b.colB
from (select 列A,列B,rn=row_number() over(order by getdate()) from 表C) a
inner join (select 列A,
rn=row_number() over(order by getdate()),
colB=row_number() over(partition by 列A order by getdate())
from 表C) b on a.rn=b.rn and a.列A=b.列A
select * from 表C
/*
列A 列B
----------- -----------
1 1
2 1
3 1
3 2
3 3
4 1
4 2
5 1
6 1
7 1
8 1
8 2
8 3
8 4
9 1
(15 row(s) affected)
*/
[/quote ]我的这个列A只是显示其中de一部分 这个列有上千条后期还会一直增加, 所以这样是不是会有行数限制呢
create table 表C(列A int,列B int)
insert into 表C(列A)
select 1 union all
select 2 union all
select 3 union all
select 3 union all
select 3 union all
select 4 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 8 union all
select 8 union all
select 8 union all
select 9
update a
set a.列B=b.colB
from (select 列A,列B,rn=row_number() over(order by getdate()) from 表C) a
inner join (select 列A,
rn=row_number() over(order by getdate()),
colB=row_number() over(partition by 列A order by getdate())
from 表C) b on a.rn=b.rn and a.列A=b.列A
select * from 表C
/*
列A 列B
----------- -----------
1 1
2 1
3 1
3 2
3 3
4 1
4 2
5 1
6 1
7 1
8 1
8 2
8 3
8 4
9 1
(15 row(s) affected)
*/
create table teem(原列A int)
insert into teem
select 1 union all
select 2 union all
select 3 union all
select 3 union all
select 3 union all
select 4 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 8 union all
select 8 union all
select 8 union all
select 9
select 原列A,
rn=row_number() over(partition by 原列A order by getdate())
from teem
/*
原列A rn
----------- --------------------
1 1
2 1
3 1
3 2
3 3
4 1
4 2
5 1
6 1
7 1
8 1
8 2
8 3
8 4
9 1
(15 row(s) affected)
*/