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
)