1.查询重复:select * from wx_user_address
WHERE
user_num IN ( SELECT a.USER_num FROM ( SELECT USER_num FROM wx_user_address GROUP BY USER_num HAVING count( 1 ) > 1 ) a )
AND id NOT IN (
SELECT
a.id
FROM
( SELECT min( id ) AS id FROM wx_user_address GROUP BY USER_num HAVING count( USER_num ) > 1 ) a
);
where USER_num in (select USER_num from wx_user_address group by USER_num having count(USER_num)>1)
2.删除重复保留一条:
DELETE
FROM
wx_user_addressWHERE
user_num IN ( SELECT a.USER_num FROM ( SELECT USER_num FROM wx_user_address GROUP BY USER_num HAVING count( 1 ) > 1 ) a )
AND id NOT IN (
SELECT
a.id
FROM
( SELECT min( id ) AS id FROM wx_user_address GROUP BY USER_num HAVING count( USER_num ) > 1 ) a
);