单字段操作
第一种方法
DELETE
FROM
tablename
WHERE
fieldname IN (
SELECT
t.dname
FROM
(
SELECT
fieldname
FROM
tablename
GROUP BY
fieldname
HAVING
count(1) > 1
) t
)
AND fieldname NOT IN (
SELECT
dt.minfieldname
FROM
(
SELECT
min(fieldname) AS minfieldname
FROM
fieldname
GROUP BY
fieldname
HAVING
count(1) > 1
) dt
)
第二种方法
DELETE
FROM
tablename
WHERE
fieldname NOT IN (
SELECT
dt.minfieldname
FROM
(
SELECT
MIN(fieldname) AS minfieldname
FROM
tablename
GROUP BY
fieldname
) dt
)
多字段操作
DELETE
FROM
tablename
WHERE
(fieldname_1, fieldname_2) IN (
SELECT
t.fieldname_1,
t.fieldname_2
FROM
(
SELECT
fieldname_1,
fieldname_2
FROM
tabledname
GROUP BY
fieldname_1,
fieldname_2
HAVING
count(1) > 1
) t
)
AND fieldname_1 NOT IN (
SELECT
dt.minfieldname_1
FROM
(
SELECT
min(fieldname_1) AS minfieldname_1
FROM
tablename
GROUP BY
fieldname_1,
fieldname_2
HAVING
count(1) > 1
) dt
)