MySQL中删除重复数据只保留一条
如下,表 temp_nsr_adv2 中 nsrsbh 相同的记录,保留ID最小的记录,其余删掉。
DELETE
FROM
temp_nsr_adv2
WHERE
nsrsbh IN (SELECT * from (
SELECT
nsrsbh
FROM
temp_nsr_adv2
GROUP BY
nsrsbh
HAVING
count(nsrsbh) > 1
) a)
AND ID NOT IN (SELECT * from (
SELECT
min(ID)
FROM
temp_nsr_adv2
GROUP BY
nsrsbh
HAVING
count(nsrsbh) > 1
) b)
注意有修改表的操作的时候,如果有子查询,要给子查询起别名,否则会报错:You can’t specify target table ‘XXX’ for update in FROM clause。
如果有多个字段判断该条记录是否重复的时候,就用多个字段分组。
DELETE
FROM
temp_nsr_adv2
WHERE
(nsrsbh, shxydm) IN (SELECT * from (
SELECT
nsrsbh, shxydm
FROM
temp_nsr_adv2
GROUP BY
nsrsbh, shxydm
HAVING
count(*) > 1
) a)
AND ID NOT IN (SELECT * from (
SELECT
min(ID)
FROM
temp_nsr_adv2
GROUP BY
nsrsbh, shxydm
HAVING
count(*) > 1
) b)