执行前
执行Sql
DELETE
FROM
test1
WHERE
EXISTS (
SELECT
*
FROM
( SELECT max( id ) id, NAME FROM test1 GROUP BY NAME HAVING count( 1 ) > 1 ) T
WHERE
test1.NAME = T.NAME
AND test1.id < T.id
)
执行后
说明:
SELECT max( id ) id, NAME FROM test1 GROUP BY NAME HAVING count( 1 ) > 1
此Sql语句的作用是查询出test1表中根据字段name来分组并且数量大于1的name数据,并且id保留最大的值
SELECT
*
FROM
( SELECT max( id ) id, NAME FROM test1 GROUP BY NAME HAVING count( 1 ) > 1 ) T
WHERE
test1.NAME = T.NAME
AND test1.id < T.id
where后的条件为查询出name数量大于1并且id非最大的数据
DELETE
FROM
test1
WHERE
EXISTS (
SELECT
*
FROM
( SELECT max( id ) id, NAME FROM test1 GROUP BY NAME HAVING count( 1 ) > 1 ) T
WHERE
test1.NAME = T.NAME
AND test1.id < T.id
)
delete后面where条件里即为数量大于1并且id非最大的数据,如果满足条件则删除