多个主键约束的情况下如何查询表里有重复的记录
由于以前的表TABLE没有设置主键.在更新数据库后表TABLE有主键A B C D..约束成唯一记录...不可以重复..
现导数据的时候发现表里有重复记录..如何查询出这些重复记录...SQL语句怎么写...
[解决办法]
select * from table where field = 'yyy'
[解决办法]
select count(e) as ecount,A,B,C,D from table
group by A,B,C,D
ecount>1的都是重复的记录。
[解决办法]
像这样写就可以了，就是麻烦点
SELECT * FROM table WHERE
A IN (SELECT A FROM table GROUP BY A HAVING (COUNT(*)>1))
OR B IN (SELECT B FROM table GROUP BY B HAVING (COUNT(*)>1))
OR C IN (SELECT C FROM table GROUP BY C HAVING (COUNT(*)>1))
OR D IN (SELECT D FROM table GROUP BY D HAVING (COUNT(*)>1))
[解决办法]
撒子数据库？ORACLE的话借助ROWID可以删除重复数据。
查看重复的记录，删除的就改一下OK了。
SELECT * FROM AAA c WHERE c.Rowid=(Select Max(Rowid) From AAA d Where d.ACCOUNT=A.ACCOUNT AND d.NAME='32423')
