Mysql 解决多行重复记录只保留最小的一条

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sno` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `sex` int(1) NOT NULL,
  `class_name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `demo`.`t_student` (`id`, `sno`, `name`, `sex`, `class_name`) VALUES (1, 1, yj, 1, 大一计算机1班);
INSERT INTO `demo`.`t_student` (`id`, `sno`, `name`, `sex`, `class_name`) VALUES (2, 2, xiaoliu, 0, 大一计算机2班);
INSERT INTO `demo`.`t_student` (`id`, `sno`, `name`, `sex`, `class_name`) VALUES (3, 3, zs, 1, 大一计算机3班);
INSERT INTO `demo`.`t_student` (`id`, `sno`, `name`, `sex`, `class_name`) VALUES (4, 4, ww, 1, 大一计算机2班);
INSERT INTO `demo`.`t_student` (`id`, `sno`, `name`, `sex`, `class_name`) VALUES (5, 5, zl, 0, 大一计算机1班);
INSERT INTO `demo`.`t_student` (`id`, `sno`, `name`, `sex`, `class_name`) VALUES (7, 5, xx, 1, xxxx);



DELETE
FROM
	t_student
WHERE
	id IN (

select id from (
		SELECT
			id
		FROM
			t_student
		WHERE
			sno IN (
				SELECT
					sno
				FROM
					t_student
				GROUP BY
					sno
				HAVING
					count(1) > 1
			)
		AND id NOT IN (
			SELECT
				min(id)
			FROM
				t_student
			GROUP BY
				sno
			HAVING
				count(1) > 1
		)
)as temp
	)

常见的问题：

ERROR 1093 (HY000): You can’t specify target table ‘code_user’ for update in FROM clause

mysql不允许对一张表同时进行查询和更新

既然不允许对一张表同时进行查询和更新，那就变成对两个表进行查询和更新。 上述语句就是将查询结果生成一个派生表，然后对派生表进行查询，然后对原表进行删除，就ok了。