加唯一索引时候发现已有重复数据删除

说话随便的人，便是没有责任心。——哈代

sql如下

代码语言：javascript复制

-- 查询重复数据id

SELECT
	GROUP_CONCAT( id SEPARATOR ',' )
FROM
	`my_table` 
GROUP BY
	`user_id`,
	`depart_id`,
	`position_id` 
HAVING
	COUNT(*) > 1 
	AND `user_id` IS NOT NULL
	AND `depart_id` IS NOT NULL
	AND `position_id` IS NOT NULL;

-- 查询需要删除掉的重复数据
SELECT
	t1.`id`,
	t1.`user_id`,
	t1.`depart_id`,
	t1.`position_id`,
	t1.`gmt_create` 
FROM
	my_table t1
	INNER JOIN (
	SELECT
		user_id,
		depart_id,
		position_id,
		MAX( gmt_create ) AS latest_gmt_create,
		MAX( id ) AS latest_id 
	FROM
		my_table 
	WHERE
		user_id IS NOT NULL 
		AND depart_id IS NOT NULL 
		AND position_id IS NOT NULL 
	GROUP BY
		user_id,
		depart_id,
		position_id 
	HAVING
		COUNT(*) > 1 
	) t2 ON t1.user_id = t2.user_id 
	AND t1.depart_id = t2.depart_id 
	AND t1.position_id = t2.position_id 
	AND ( t1.gmt_create < t2.latest_gmt_create OR t1.id < t2.latest_id );
	
-- 改为删除sql，执行删除
DELETE
	t1
FROM
	my_table t1
	INNER JOIN (
	SELECT
		user_id,
		depart_id,
		position_id,
		MAX( gmt_create ) AS latest_gmt_create,
		MAX( id ) AS latest_id 
	FROM
		my_table 
	WHERE
		user_id IS NOT NULL 
		AND depart_id IS NOT NULL 
		AND position_id IS NOT NULL 
	GROUP BY
		user_id,
		depart_id,
		position_id 
	HAVING
		COUNT(*) > 1 
	) t2 ON t1.user_id = t2.user_id 
	AND t1.depart_id = t2.depart_id 
	AND t1.position_id = t2.position_id 
	AND ( t1.gmt_create < t2.latest_gmt_create OR t1.id < t2.latest_id );                     

null position table 数据 索引

0 人点赞