sqlserver 2005 本来应该为主键的字段出现重复

Question

sqlserver 2005 本来应该为主键的字段出现重复，怎么将字段重复的记录查询出来，并将重复的字段随即修改成不同的值。

示例表：com_invsale
重复字段 fbillid;

Answer 1

-- 方式1
select fbillid,count(fbillid) from com_invsale
group by fbillid having count(fbillid)>1

-- 方式2
select * from
(
select count(fbillid) over(partition by fbillid ) c1,
t.*
from com_invsale t
) t1
where c1 > 1

-- 修改方式
这个要看一下源表中是否存在唯一的符合字段列表；如果存在，则相对简单，用update可以解决
否则相对复杂，可以参考下面的解决方法。

当SQLServer设计表的时候没有建组合字段唯一约束，以后需要增加这一约束时，却发现表里已经有了很

多重复记录了。

请看看我用的去掉SQLServer表里组合字段重复的记录方法：

假设原始表名为source_table,字段名1为field_name1,字段名2为field_name2。

（当然稍加修改也可以用到三个及以上组合字段重复的情况）

第一步: 生成组合字段重复的临时表source_dup_simple
select field_name1,field_name2,count(0) as num into source_dup_simple
from source_table
group by field_name1,field_name2 having count(0)>1

第二步: 生成组合字段重复的主表里完整记录的临时表source_table_dup
select t1.* into source_table_dup
from source_table t1,source_dup_simple t2
where t1.field_name1=t2.field_name1 and t1.field_name2=t2.field_name2

第三步: 删去source_table_dup里的全部重复记录
delete from source_table_dup
where convert(varchar,field_name1)+convert(varchar,field_name2) in
(select convert(varchar,field_name1)+convert(varchar,field_name2)
from source_dup_simple)

第四步: 生成有序列号的重复组合字段记录表source_table_dup_2
select IDENTITY(int,1,1) as rowid,t1.* into source_table_dup_2
from source_table_dup t1
order by field_name1,field_name2,date_field_name

说明：用自动增长序列号IDENTITY(int,1,1)生成唯一的行号字段rowid
这里是按字段field_name1,field_name2,date_field_name排序，
以方便后面删除最新还是最旧时间的重复记录

当然date_field_name字段可以替换成你想要排序的字段，并可用desc选项

第五步: 删去有序列号重复组合字段记录表source_table_dup_2里面的重复记录
delete from source_table_dup_2
where rowid in (select min(rowid) from source_table_dup_2
group by field_name1,field_name2 having count(*)>1)

（所影响的行数为 5586 行）

delete from source_table_dup_2
where rowid in (select min(rowid) from source_table_dup_2
group by field_name1,field_name2 having count(*)>1)
（所影响的行数为 1108 行）

...... ......

delete from source_table_dup_2
where rowid in (select min(rowid) from source_table_dup_2
group by field_name1,field_name2 having count(*)>1)
（所影响的行数为 0 行）

注意：上面这条删除的SQL要执行一到多次，因为组合字段重复记录可能一条以上，
一直到它（所影响的行数为 0 行）才算彻底删除干净重复记录。

我这里是保留重复记录里时间字段date_field_name最新的记录。

第六步: 把剩下的没有重复的记录插回原始表
insert into source_table(field_name1,field_name2,.....)
select field_name1,field_name2,...... from source_table_dup_2

简单方法：

1、建立一个与当前表结构完全一致临时表
2、用distinct关键字，将当前表中数据导入到临时表
3、删除当前表数据
4、从临时表将数据存回当前表

begin tran
select distinct * into #t from YOUR_TABLE
trancate table YOUR_TABLE
insert into YOUR_TABLE
select * from #t
if @@error<>0
begin
rollback tran
drop table #t
return
end
commit tran
drop table #t