查询并删除数据库中的重复记录

//查询单列重复记录
SELECT COLUMN_1,COUNT(*) FROM TABLE_NAME GROUP BY COLUMN_1 HAVING COUNT(*)>1

//查找两列重复记录
SELECT COLUMN_1,COLUMN_2,COUNT(*) FROM TABLE_NAME GROUP BY COLUMN_1,COLUMN_2 HAVING COUNT(*)>1

//查找重复记录的详细信息
SELECT * FROM TABLE_NAME
WHERE COLUMN_1 IN (SELECT CITY_ID
    FROM EQUIP_F_ELECT_RATIO
    GROUP BY COLUMN_1
    HAVING COUNT(*)>1)

//删除数据库中重复记录
select corpname,count(*) from tbcorp group by corpname having count(*) > 1
delete from tbcorp a where a.rowid!=(select min(b.rowid) from tbcorp b where a.corpname = b.corpname)

//删除数据库中重复记录2
delete from 表 where id not in(select min(id) from 表 group by 字段1,字段2,字段3)

[ 本帖最后由 灰儿 于 2008-4-15 22:55 编辑 ]