SQL:删除表中重复数据的记录,并保留

解决程序异常导致的表内数据重复的问题

表内数据量小时

• 删除语句

  DElETE
  	* 
  FROM
  	USER u 
  WHERE
  	u.username IN ( 
  	SELECT USER.username 
  	FROM USER 
  	GROUP BY USER.username 
  	HAVING count( USER.username ) > 1
  	 ) 
  	AND
  	 u.id NOT IN (
  	SELECT min( id ) AS id 
  	FROM USER 
  	GROUP BY 
  	USER.username 
  	HAVING count( USER.username ) > 1 
  	)
  )
  

解析

• 根据重复条件进行分组,过滤出重复的数据(例:username为重复的字段)

  u.username IN ( 
  SELECT USER.username 
  FROM USER 
  GROUP BY USER.username 
  HAVING count( USER.username ) > 1
  ) 
  

• 保留一个数据,通过集合函数进行指定,例:指定的最小的id的记录数据保留

  u.id NOT IN (
  SELECT min( id ) AS id 
  FROM USER 
  GROUP BY 
  USER.username 
  HAVING count( USER.username ) > 1 
  )
  

表内数据量大时

• 对上面的代码进行内连接操作,优化执行效率

  SELECT
  	* 
  FROM
  	USER u,
  	(
  	SELECT 
  		username,
  		min( id ) AS id 
  	FROM
  	USER 
  	GROUP BY
  		USER.username 
  	HAVING
  		count( * ) > 1 
  	) un 
  WHERE
  	u.id != un.id 
  	AND u.username = un.username
  

解析

• 进行查询重复数据内容(指定字段username)和保留的条件指定min( id ) AS id,指定其他重复数据,需要修改查询的显示内容,以及分组的条件

  SELECT 
  	username,
  	min( id ) AS id 
  FROM
  USER 
  GROUP BY
  	USER.username 
  HAVING
  	count( * ) > 1 
  

• 进行重复数据字段指定,不删除保留的数据,其他条件需要添加更多条件,与上面的SQL查询显示的数据对应

  u.id != un.id 
  AND u.username = un.username