sql 删除多条重复记录，只保留一条

1.按单个字段进行分组

查询：

SELECT max(Id) Id,ModelNo FROM Pricemodi
     where UserName = 'GPrice' 
    GROUP BY ModelNo
    HAVING COUNT(id) >  1

删除： 

DELETE C FROM Pricemodi C 
JOIN (
    SELECT max(Id) Id,ModelNo FROM Pricemodi
     where UserName = 'GPrice' 
    GROUP BY ModelNo
    HAVING COUNT(id) >  1
) TMP ON C.ModelNo = TMP.ModelNo AND C.Id <> TMP.Id

2.按照多个字段进行分组

查询多列重复

SELECT a.* FROM test a,( SELECT name,code FROM test GROUP BY name,code HAVING COUNT(1)>1 ) AS b WHERE a.name=b.name AND a.code=b.code