有个需求：选出表里重复的数据，只保留一个

having是在分组之后，对组内数据进行筛选，where 就不多说了，在分组前添加条件的

SELECT id,name,count(name) FROM idiom group by name HAVING count(name)>=2 可以看出大概519个数据都是有重复的

SELECT id,name,min(id),max(id),count(name) FROM idiom group by name HAVING count(name)>=3 可以看出默认选出的id就是最小的

重复两条的数据有点多，直接上3个跟上头有个对比 SELECT id,name FROM idiom WHERE name IN (SELECT name FROM idiom GROUP BY name HAVING COUNT(name)>=3) AND id NOT IN (SELECT id FROM idiom GROUP BY name HAVING COUNT(name)>=3)

一切正常，执行删除：DELETE FROM idiom WHERE name IN (SELECT name FROM idiom GROUP BY name HAVING COUNT(name)>=2) AND id NOT IN (SELECT id FROM idiom GROUP BY name HAVING COUNT(name)>=2)

但是，好像报错啦：

没办法，这个报错查了好多好像是要使用临时表于是改成这个样子

1
2
3
4
5
6
7
8

DELETE FROM idiom WHERE id IN (
    SELECT x.id FROM
    	(SELECT id,name FROM idiom WHERE name IN (
            SELECT name FROM idiom GROUP BY name HAVING COUNT(name)>=2
        )AND id NOT IN (
             SELECT id FROM idiom GROUP BY name HAVING COUNT(name)>=2)
        ) x
)