删除重复数据只保留id最大的那一条记录
DELETE
FROM
table1
WHERE
( sid,name,age ) IN (
SELECT
sid,name,age
FROM
( SELECT sid,name,age FROM table1 GROUP BY sid,name,age HAVING count( * ) > 1 ) t
)
AND id NOT IN (
SELECT
t.id
FROM
( SELECT max( id ) AS id, sid,name,age FROM table1GROUP BY sid,name,age HAVING count( * ) > 1 ) t
)
需要注意的点:
1:如果没有将查出的结果作为临时表会报错
> 1093 - You can't specify target table 't' for update in FROM clause
意思是查询的时候不能进行更新操作