对表1
alter table 表1 drop column authorid
alter table 表1 add authorid int identity(1,1)
对表2
truncate table 表2
insert into 表2 select authorid from 表1
alter table 表1 drop column authorid
alter table 表1 add authorid int identity(1,1)
对表2
truncate table 表2
insert into 表2 select authorid from 表1
insert @t1
select '10000','218'
union all select '10001','218'
union all select '10002','218'
union all select '10003','219'
union all select '10004','219' select * ,authorid + (select count(1) from @t1 where authorid = t.authorid and paperid <t.paperid )
from @t1 t
go
create table [tb1]([paperid] int,[authorid] int)
insert [tb1] select 10000,218
union all select 10001,218
union all select 10002,218
union all select 10003,219
union all select 10004,219
go
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([authorid] int)
insert [tb2] select 218
union all select 219
go
--插入辅助字段
alter table tb1 add fid int
go
--更新辅助字段
update tb1 set fid=1 where exists(select 1 from tb1 t where t.[authorid]=tb1.[authorid] and t.[paperid]<tb1.[paperid])
--更新最终结果
declare @i int
select @i=max([authorid]) from tb1
update tb1 set [authorid]=@i,@i=@i+1 where fid=1
--插入结果到tb2
insert tb2 select [authorid] from tb1 where fid=1
--删除辅助字段
alter table tb1 drop column fid
--查询tb1结果
select * from tb1
/*
paperid authorid
----------- -----------
10000 218
10001 220
10002 221
10003 219
10004 222(5 行受影响)
*/
--查询tb2结果
select * from tb2
/*
authorid
-----------
218
219
220
221
222(5 行受影响)
*/
insert @t1
select '10000','218'
union all select '10001','218'
union all select '10002','218'
union all select '10003','219'
union all select '10004','219' select * ,(select min(authorid) from @t1) + (select count(1) from @t1 where paperid <t.paperid )
from @t1 t/* 结果
(5 row(s) affected)paperid authorid
---------- ----------- -----------
10000 218 218
10001 218 219
10002 218 220
10003 219 221
10004 219 222(5 row(s) affected)*/
insert @t1
select '10000','218'
union all select '10001','218'
union all select '10002','218'
union all select '10003','219'
union all select '10004','219' select (select min(authorid) from @t1) + (select count(1) from @t1 where paperid <t.paperid ) as authorid
from @t1 t
authorid
-----------
218
219
220
221
222(5 行受影响)
*/
INSERT INTO @tab(ID,KEYID)
SELECT 10000,218 UNION ALL
SELECT 10001,218 UNION ALL
SELECT 10002,218 UNION ALL
SELECT 10003,219 UNION ALL
SELECT 10004,219
;WITH CTE AS
(
SELECT ID,KEYID,ROW_NUMBER()OVER(PARTITION BY KEYID ORDER BY KEYID) X
FROM @TAB
)
,
CTE2 AS
(
SELECT *,ROW_NUMBER()OVER (ORDER BY GETDATE()) T FROM CTE WHERE X<>1
) UPDATE @TAB SET X=CASE WHEN ISNULL(B.T,0)<>0 THEN (SELECT MAX(KEYID) FROM @TAB)
ELSE 0 END + CASE WHEN ISNULL(B.T,0)<>0 THEN ISNULL(B.T,0) ELSE A.KEYID END
FROM @TAB A LEFT JOIN
CTE2 B ON A.ID=B.ID AND A.KEYID=B.KEYID
SELECT * FROM @TAB
/*
id keyid X
----------- ----------- -----------
10000 218 218
10001 218 220
10002 218 221
10003 219 219
10004 219 222(5 行受影响)*/
ALTER TABLE [TB1]
DROP COLUMN authorid;
--2.TB1中添加新的authorid标识列
ALTER TABLE [TB1]
ADD authorid INT IDENTITY(218,1);
--3.删除TB1
DROP TABLE [TB2]
--4.创建[TB2]并插入数据
SELECT authorid INTO [TB2] FROM [TB1]
--===================================================
SELECT * FROM TB1paperid authorid
----------- -----------
10000 218
10001 219
10002 220
10003 221
10004 222
(5 行受影响)SELECT * FROM TB2authorid
-----------
218
219
220
221
222
(5 行受影响)