数据库查寻重复的数据，删除其它重复数据只保留一条最新记录

最新推荐文章于 2024-08-07 14:51:18 发布

倚楼北望

最新推荐文章于 2024-08-07 14:51:18 发布

阅读量689

点赞数 1

原文链接：https://www.cnblogs.com/jiangxiaobo/p/6589541.html

版权

查找表中多余的重复记录

SELECT
    *
FROM
    people
WHERE
    peopleId IN (
        SELECT
            peopleId
        FROM
            people
        GROUP BY
            peopleId
        HAVING
            count(peopleId) > 1
    )

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

delete from table_name a
where a.name in
(select name from table_name group by name having count(colum_name) > 1)
and rowid not in
(select min(rowid) from table_name group by name having count(colum_name) > 1)

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

SELECT
    *
FROM
    vitae a
WHERE
    (a.peopleId, a.seq) IN (
        SELECT
            peopleId,
            seq
        FROM
            vitae
        GROUP BY
            peopleId,
            seq
        HAVING
            count(*) > 1
    )

删除表中多余的重复记录（多个字段），只留有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
)

查找表中多余的重复记录（多个字段），不包含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
)

消除一个字段的左边的第一位：

UPDATE tableName
SET [ Title ]= RIGHT ([ Title ],(len([ Title ]) - 1))
WHERE
    Title LIKE '村%'

消除一个字段的右边的第一位：

UPDATE tableName
SET [ Title ]= LEFT ([ Title ],(len([ Title ]) - 1))
WHERE
    Title LIKE '%村'

假删除表中多余的重复记录（多个字段），不包含rowid最小的记录

UPDATE vitae
SET ispass =- 1
WHERE
    peopleId IN (
        SELECT
            peopleId
        FROM
            vitae
        GROUP BY
            peopleId