mysql 更新标记重复记录最小id的记录--删除重复记录

SELECT  * from t_user tu  where tu.phone  = '15158243878';

SELECT  * from t_user tu  where tu.phone  = '13017708283';

SELECT  * from t_device td  where user_id  = '1038091';

SELECT t1.*,t2.* from
		t_device t1
		inner join (SELECT min(id) id,user_id,count(0) FROM t_device  WHERE  id  <1055500 GROUP BY user_id HAVING count(1) >1) t2
		on t1.user_id = t2.user_id;

--   DELETE t1 
	SELECT t1.*,t2.*  
from t_device t1 
		inner join (SELECT max(id) id,user_id,count(0) FROM t_device  WHERE  user_id = '1000270' GROUP BY user_id HAVING count(1) >1) t2
		on t1.user_id = t2.user_id
		where t1.id < t2.id;	
		
	SELECT max(id) id,user_id,count(0) FROM t_device  WHERE  user_id = '1000035' GROUP BY user_id 

— 删除 最小id 重复记录

	-- UPDATE t1 set t1.unique_id = CONCAT( t1.unique_id, '==') 
	DELETE t1 
from t_device t1 
		inner join (SELECT max(id) id,user_id,count(0) FROM t_device  WHERE user_id = '1000035' GROUP BY user_id HAVING count(1) >1) t2
		on t1.user_id = t2.user_id
		where t1.id < t2.id;	

— 更新 最小id 重复记录

UPDATE  t_device t1 
		inner join (SELECT max(id) id,user_id,count(0) FROM t_device  WHERE user_id = '1000270' GROUP BY user_id HAVING count(1) >1) t2
		on t1.user_id = t2.user_id
		set t1.unique_id =  CONCAT( unique_id, '==')
		where t1.id < t2.id;	
	
	-- 1049534	1000035 
	-- 1000596	1000035
	SELECT    CONCAT( unique_id, '==') ,id, user_id  from t_device td  where user_id  = '1000035';

— 更新 最小id 重复记录

UPDATE  t_device t1 
	inner join (SELECT max(id) id,user_id,count(0) FROM t_device    GROUP BY user_id HAVING count(1) >1) t2
	on t1.user_id = t2.user_id
	set t1.unique_id =  CONCAT( t1.unique_id, '=='), t1.nick_name =  CONCAT( t1.nick_name , '==同手机号多个微信')
	where t1.id < t2.id;	

**

– 查询重复数据

**

		SELECT t1.*,t2.*  
from t_device t1 
		inner join (SELECT max(id) id,user_id,count(0) FROM t_device   GROUP BY user_id HAVING count(1) >1) t2
		on t1.user_id = t2.user_id

— 查询 最小id 废弃数据

SELECT t1.*,t2.*  
from t_device t1 
		inner join (SELECT max(id) id,user_id,count(0) FROM t_device   GROUP BY user_id HAVING count(1) >1) t2
		on t1.user_id = t2.user_id
		where t1.id < t2.id;