如何查找出表中的重复记录？

[xy5418]

表test中有a,b,c,d四个字段，其中有重复记录，如何查找出表中的重复记录？

[OpenVMS]

SELECT * INTO #TEMP FROM test
ALTER TABLE #TEMP ADD ID INT IDENTITY(1,1)

SELECT * FROM #TEMP t
WHERE ID<(SELECT MAX(ID) FROM #TEMP
WHERE a=t.a AND b=t.b AND c=t.c AND d=t.d
GROUP BY a,b,c,d)

DROP TABLE #TEMP

[zhdleo]

楼上（斑竹），我也学习一下，你这

SELECT * INTO #TEMP FROM test
ALTER TABLE #TEMP ADD ID INT IDENTITY(1,1)
怎么个解释，洗耳恭听。。学习学习

[CSDNM]

不必这么麻烦吧

select a,b,c,d from test group by a,b,c,d having count(*)>0

[CSDNM]

更正:

select a,b,c,d from test group by a,b,c,d having count(*)>1

[j9988]

select * from tablename group by a,b,c,d having count(*)>1

[OpenVMS]

TO: zhdleo(叮东)把原来的表复制到临时表里,在临时表里增加一个自动增长的ID号

[zhdleo]

好的明白，
那么另外是不是也可以直接用系统的inserted 表呢？

[billyliu]

增加一个自动增长字段:
alter table table_name add IDFIELD integer identity(1,1)
，删除重复记录
delete from table_name a
where IDFIELD< (select max(IDFIELD) from table_name
where column1=a.column1 and column2=a.column2
and colum3=a.colum3 and ...);
最后执行
alter table table_name drop column IDFIELD 删除增加的字段。

方法2：
创建临时表
select distinct * into tmp_table_name from table_name
然后，truncate table table_name
最后，insert into table_name select * from tmp_table_name

[zhdleo]

select * from tablename m,inserted n
where m.a=n.a and m.b=n.b and m.c=n.c and m.d=n.d

[zhdleo]

还有就是，你这个表总有个主键吧，
那where里就可以不检查主键，那本来就是不能重复的。

[1825]

查找a重复记录
select a from test group by a having count(*)>1
类推

[prjM]

1825() ( ）的方法可以。

[billyliu]

select * from test where (a in (select a from test group by a
having count(a)>1))and (b in (select b from test group by b
having count(b)>1))and (c in (select c from test group by c
having count(c)>1))and (d in (select d from test group by d
having count(d)>1))and

[1825]

select a.* from test a left join (select a,b,c,d from test group by a,b,c,d having count(*)>1 ) b on a.a=b.a and a.b=b.b and a.c=b.c and a.d=b.d where b.a is not null

[OpenVMS]

only used in trigger