问题1:
有表如下:
create Ta
(
id int identity(1,1) not null,
code varchar(10) null,
num float null
)
insert Ta(code,num)
select '001',100.00 union all
select '001',100.00 union all
select '001',500.00 union all
select '002',200.00 union all
select '002',200.00
/* 删除Ta表中字段 code,num 重复记录,保留ID值最小的记录*/
方法一:
select min(ID) RID ,code,num into #a from ta group by code,num having count(*)>1
delete from Ta where ID>(select RID from #a where #a.code=Ta.code and #a.num=Ta.num)
方法二:
delete from Ta where exists(select 1 from Ta T where Ta.code=T.code and Ta.num=T.num and Ta.ID>T.ID)
方法三:
with tb as(
select row_number() over(partition by code,num order by code,num,a ) newID,code,num from ta
)
delete from tb where newID>1
问题2:
有表TB 如下:
create table Tb( code varchar(10) null,num float null)
insert Tb(code,num)
select '001',100.00 union all
select '001',100.00 union all
select '002',100.00 union all
select '002',200.00 union all
select '002',100.00
go
/* 删除Tb表中所有字段 存在重复记录 */
方法一: 连同重复的记录也一并删除了。
select code,num, count(*) as nid into #a from Ta group by code,num having count(*)>1
delete from Tb where exists(select 1 from #a where #a.code=code and #a.num=num)
方法二: 连同重复的记录也一并删除了。
with ch as(
select code,num from tb group by code,num having count(*)>1
)
delete from tb where not exists(select 1 from ch where ch.code=tb.code and ch.num=tb.num)
/* 删除Tb表中所有字段 重复记录 ,保留一条记录*/
方法一:
select distinct * into #a from tb
drop table tb
select * into tb from #a
drop table #a
方法二:
with ch as(
select row_number() over(partition by code,num order by code,num ) newID,* from tb
)
delete from ch where m>1