|
上周工作中数据库中出现了N多重复记录的情况，导致联合查询时数据异常。由于数据是客户提供的，当时并没有提供唯一标识列，而且也没预料到会出现重复数据。哎，大意了。后来对表中的数据进行了一次重复查询。
1、查找表中重复记录，重复记录是根据单个字段来判断，并统计重复次数
SELECT [字段],COUNT(0) AS 重复次数 FROM [表名] GROUP BY [字段] HAVING COUNT([字段]) > 1
2、删除表中多余的重复记录，重复记录是根据单个字段来判断，只留有rowid最小的记录
DELETE FROM [表名] WHERE [字段] IN ( SELECT [字段] FROM [表名] GROUP BY [字段] HAVING COUNT([字段]) > 1 )
AND rowid NOT IN ( SELECT MIN(rowid) FROM [表名] GROUP BY [字段] HAVING COUNT([字段]) > 1 )
3、查询无重复记录，根据单个字段查询
SELECT DISTINCT [字段] FROM [表名] ORDER BY [字段] DESC
PS:血淋淋的教训，不管别人提供的数据里面否真的都是唯一的，一定要唯一主键或标识列
|