34,589
社区成员
发帖
与我相关
我的任务
分享
if object_id('test') is not null
drop table test
if object_id('tempdb..#t') is not null
drop table #t
if object_id('tempdb..#t1') is not null
drop table #t1
go
create table test(id int,name varchar(10))
insert into test
select 1,'A'
union all select 1,'A'
union all select 1,'A'
union all select 2,'B'
select id1=identity(int,1,1),id,name into #t from test
select * from #t
update #t
set id=null
from #t m
where exists(select 1 from #t where m.name=#t.name and m.id1>#t.id1)
select id,name into #t1 from #t
select *
from #t1
(所影响的行数为 4 行)
(所影响的行数为 4 行)
id1 id name
----------- ----------- ----------
1 1 A
2 1 A
3 1 A
4 2 B
(所影响的行数为 4 行)
(所影响的行数为 2 行)
(所影响的行数为 4 行)
id name
----------- ----------
1 A
NULL A
NULL A
2 B
(所影响的行数为 4 行)