重复记录查询，删除

1.查询某字段重复的数据。

SELECT * FROM    A,(SELECT M ,N FROM   A   GROUP BY   M,N     HAVING  COUNT(*) > 1  ) A1  WHERE   A.M = A1.M    AND    A.N = A1.N;

 

SELECT   *   FROM  TABLENAME  A  WHERE  (A.PEOPLELD,A.SEQ)  IN  (SELECT  PEOPLELD,SEQ   FROM   TABLENAME   GROUP  BY   PEOPLELD  ,SEQ   HAVING   COUNT(*)  > 1);

 

SELECT *   FROM   TABLENAME    A   WHERE (A.PEOPLELD,A.SEQ)   IN  ( SELECT  PEOPLELD,SEQ FROM TABLENAME  GROUP  BY  PEOPLELD ,SEQ  HAVING   COUNT(*) > 1)   AND   ROWID  NOT   IN   ( SELECT MIN(ROWID)  FROM  TABLENAME  GROUP   BY   PEOPLELD,SEQ    HAVING   COUNT(*)  > 1);

 

2.转移不重复的数据到临时表,得到不重复数据。

SELECT   DISTINCT * INTO   #TEMP   FROM  TableName;

TRUNCATE    TABLE   TableName;

//DELETE   FROM   TableName;

INSERT   TableName  SELECT * FROM    #TEMP ;

DROP   TABLE    #TEMP;

 

3.查询重复数据。

SELECT    *  FROM   TABLE1     GROUP  BY   USERNAME    HAVING     COUNT(*)>1;

 

4.删除重复数据只保留一条

DELETE   FROM   TABLE1   WHERE    NAME    NOT   IN  (SELECT  MAX ( NAME )    FROM   TABLE1);

 

DELETE    FROM   TABLE1   WHERE  NAME   NOT   IN  (SELECT  MIN(NAME)   FROM   TABLE1);

 

DELETE   FROM   PEOPLE    WHERE   PEOPLELD    IN   ( SELECT   PEOPLELD   FROM   PEOPLE   GROUP    BY   PEOPLELD    HAVING   COUNT(PEOPLELD)   > 1)  AND   ROWID   NOT   IN  (SELECT  MIN(ROWID)  FROM   PEOPLE   GROUP  BY    PEOPLELD   HAVING    COUNT(PEOPLELD)  > 1);

 

DELETE   FROM   TABLENAME    A   WHERE (A.PEOPLELD,A.SEQ)   IN  ( SELECT  PEOPLELD,SEQ FROM TABLENAME  GROUP  BY  PEOPLELD ,SEQ  HAVING   COUNT(*) > 1)   AND   ROWID  NOT   IN   ( SELECT MIN(ROWID)  FROM  TABLENAME  GROUP   BY   PEOPLELD,SEQ    HAVING   COUNT(*)  > 1);

 

5.查询不重复数据

SELECT      DISTINCT  *   FROM   TableName;