删除重复记录10.22
现在表中存在多条重复记录
A B C D
--- ---- ---- ----
A123 123456 北京 天天
A123 123456 北京 天天
A123 123456 北京 天天
A124 123457 北京
A124 123457 北京
A125 123458 上海 圆圆
A125 123458 上海
现在就是要删除重复记录(A,B,C字段重复),如果D不为空的话,则优先保存D不为空的记录
结果如下:
A B C D
--- ---- ---- ----
A123 123456 北京 天天
A124 123457 北京
A125 123458 上海 圆圆
方法:-
go
create table tb(A nvarchar(8), B nvarchar(8), C nvarchar(16), D nvarchar(16))
insert tb select 'A123', '123456', '北京', '天天'
union all select 'A123', '123456', '北京', '天天'
union all select 'A123', '123456', '北京', '天天'
union all select 'A124', '123457', '北京', null
union all select 'A124', '123457', '北京', null
union all select 'A125', '123458', '上海', '圆圆'
union all select 'A125', '123458', '上海', null
--select * from tb
select A,B,C,max(D) as D into #T from tb group by A,B,C
truncate table tb
insert tb select * from #T
select * from tb
drop table #T
drop table tb
create table tb(A nvarchar(8), B nvarchar(8), C nvarchar(16), D nvarchar(16))
insert tb select 'A123', '123456', '北京', '天天'
union all select 'A123', '123456', '北京', '天天'
union all select 'A123', '123456', '北京', '天天'
union all select 'A124', '123457', '北京', null
union all select 'A124', '123457', '北京', null
union all select 'A125', '123458', '上海', '圆圆'
union all select 'A125', '123458', '上海', null
--select * from tb
select A,B,C,max(D) as D into #T from tb group by A,B,C
truncate table tb
insert tb select * from #T
select * from tb
drop table #T
drop table tb
方法:二
select a.* from t a where d=(select top 1 d from t where a=a.a and b=a.b and c=a.c)
--取最小
select a.* from t a where d=(select min(d) from t where a=a.a and b=a.b and c=a.c)
--取最大
select a.* from t a where d=(select max(d) from t where a=a.a and b=a.b and c=a.c)
--随机取
select a.* from t a where d=(select top 1 d from t where a=a.a and b=a.b and c=a.c order by newid())
--取最小
select a.* from t a where d=(select min(d) from t where a=a.a and b=a.b and c=a.c)
--取最大
select a.* from t a where d=(select max(d) from t where a=a.a and b=a.b and c=a.c)
--随机取
select a.* from t a where d=(select top 1 d from t where a=a.a and b=a.b and c=a.c order by newid())