SQL查询和删除重复字段的内容

--例如：

id           NAME         VALUE

1               a                 pp

2               a                 pp

3               b                 iii

4               b                 pp

5               b                 pp

6               c                 pp

7               c                 pp

8               c                 iii

--id是主键

--要求得到这样的结果

id           NAME         VALUE

1               a                 pp

3               b                 iii

4               b                 pp

6               c                 pp

8               c                 iii

--方法1

DELETE YourTable

WHERE  [id] NOT   IN (SELECT MAX([id])

                      FROM   YourTable

                      GROUP BY

                             (NAME + VALUE)) 

--方法2

DELETE a

FROM   表 a

       LEFT   JOIN (

                SELECT id = MIN(id)

                FROM   表

                GROUP BY

                       NAME,

                       VALUE

            )b

            ON  a.id = b.id

WHERE  b.id IS NULL 

--查询及删除重复记录的SQL语句

--查询及删除重复记录的SQL语句

--1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

SELECT *

FROM   people

WHERE  peopleId IN (SELECT peopleId

                    FROM   people

                    GROUP BY

                           peopleId

                    HAVING COUNT(peopleId) > 1)

--2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

DELETE

FROM   people

WHERE  peopleId IN (SELECT peopleId

                    FROM   people

                    GROUP BY

                           peopleId

                    HAVING COUNT(peopleId) > 1)

       AND rowid NOT IN (SELECT MIN(rowid)

                         FROM   people

                         GROUP BY

                                peopleId

                         HAVING COUNT(peopleId) > 1)

--3、查找表中多余的重复记录（多个字段）

SELECT *

FROM   vitae a

WHERE  (a.peopleId, a.seq) IN (SELECT peopleId,

                                      seq

                               FROM   vitae

                               GROUP BY

                                      peopleId,

                                      seq

                               HAVING COUNT(*) > 1)

--4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

DELETE

FROM   vitae a

WHERE  (a.peopleId, a.seq) IN (SELECT peopleId,

                                      seq

                               FROM   vitae

                               GROUP BY

                                      peopleId,

                                      seq

                               HAVING COUNT(*) > 1)

       AND rowid NOT IN (SELECT MIN(rowid)

                         FROM   vitae

                         GROUP BY

                                peopleId,

                                seq

                         HAVING COUNT(*) > 1)

--5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录

SELECT *

FROM   vitae a

WHERE  (a.peopleId, a.seq) IN (SELECT peopleId,

                                      seq

                               FROM   vitae

                               GROUP BY

                                      peopleId,

                                      seq

                               HAVING COUNT(*) > 1)

       AND rowid NOT IN (SELECT MIN(rowid)

                         FROM   vitae

                         GROUP BY

                                peopleId,

                                seq

                         HAVING COUNT(*) > 1)

--(二)

--比方说

--在A表中存在一个字段“name”，

--而且不同记录之间的“name”值有可能会相同，

--现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；

SELECT NAME,

       COUNT(*)

FROM   A

GROUP BY

       NAME

HAVING COUNT(*) > 1

--如果还查性别也相同大则如下:

SELECT NAME,

       sex,

       COUNT(*)

FROM   A

GROUP BY

       NAME,

       sex

HAVING COUNT(*) > 1(三)

--方法一

DECLARE @max     integer,

        @id      integer

DECLARE cur_rows CURSOR LOCAL

FOR

    SELECT 主字段,

           COUNT(*)

    FROM   表名

    GROUP BY

           主字段

    HAVING COUNT(*) > ； 1

OPEN cur_rows

FETCH cur_rows INTO @id,@max

WHILE @@fetch_status = 0

BEGIN

    SELECT @max = @max -1

    SET ROWCOUNT @max

    DELETE

    FROM   表名

    WHERE  主字段 = @id

    FETCH cur_rows INTO @id,@max

END

CLOSE cur_rows

SET ROWCOUNT 0

--方法二

--＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。

--1、对于第一种重复，比较容易解决，使用

SELECT DISTINCT *

FROM   tableName

--就可以得到无重复记录的结果集。

--如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除

SELECT DISTINCT * INTO #Tmp

FROM   tableName

DROP TABLE tableName

SELECT * INTO tableName

FROM   #Tmp

DROP TABLE #Tmp

--发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。

--2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下

--假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集

SELECT IDENTITY(INT, 1, 1)  AS autoID,

       * INTO #Tmp

FROM                           tableName

SELECT MIN(autoID) AS autoID INTO #Tmp2

FROM   #Tmp

GROUP BY

       NAME,

       autoID

SELECT *

FROM   #Tmp

WHERE  autoID IN (SELECT autoID

                  FROM   #tmp2)

--最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）(四)

--查询重复

SELECT *

FROM   tablename

WHERE  id IN (SELECT id

              FROM   tablename

              GROUP BY

                     id

              HAVING COUNT(id) > 1)