mysql查询字段重复和删除重复字段

/*查询所有uid 重复的条目*/
SELECT * FROM xm_user_referee_verify where uid in (SELECT uid FROM xm_user_referee_verify GROUP BY uid HAVING COUNT(uid)>1) ORDER BY uid;

/*查询所有 referee_secondly_id 重复的条目*/
SELECT * FROM xm_user_referee_verify where referee_secondly_id in (SELECT referee_secondly_id FROM xm_user_referee_verify GROUP BY referee_secondly_id HAVING COUNT(referee_secondly_id)>1) ORDER BY referee_secondly_id;

/*查询 */
SELECT * FROM xm_user_referee_verify WHERE (uid,referee_secondly_id) in (SELECT uid,referee_secondly_id FROM xm_user_referee_verify GROUP BY uid,referee_secondly_id HAVING count(*)>1)

/*给raise_uid 字段设值*/
UPDATE `xm_user_referee_verify` SET `raise_uid`=`referee_secondly_id` WHERE raise_uid=0 OR raise_uid IS NULL

留下评论