删除多余数据,只保留id最小的数据

删除多余数据,只保留id最小的数据

一、整体sql

delete 
from
	student 
where
	( name, age ) in ( select name, age from ( select name, age from student group by name, age having count(*) > 1 ) temp ) 
	AND id NOT in (
	SELECT id 
	FROM
	( select min( id ) id from student group by name, age having count(*) > 1 ) temp )

二、sql拆分解释：

查出所有满足条件的数据的临时表

( select name, age from ( select name, age from student group by name, age having count(*) > 1 ) temp )

查出id最小的数据

( select min( id ) id from student group by name, age having count(*) > 1 ) temp

如果对你有帮助，请给个赞。