常用sql语句

//查询重复数据

SELECT * FROM WxAccount u WHERE u.`sessionKey` IN (SELECT sessionKey FROM WxAccount t GROUP BY sessionKey HAVING COUNT(*)>1)
AND u.`wxOpenid` IN (SELECT t2.wxOpenid FROM WxAccount t2 GROUP BY t2.wxOpenid HAVING COUNT(*)>1)

 

//删除多于数据 只留有 id 最小的记录

DELETE FROM WxAccount WHERE id IN
(SELECT u6.id FROM
(SELECT * FROM WxAccount u WHERE u.`sessionKey` IN (SELECT sessionKey FROM WxAccount t GROUP BY sessionKey HAVING COUNT(*)>1)
AND u.`wxOpenid` IN (SELECT t2.wxOpenid FROM WxAccount t2 GROUP BY t2.wxOpenid HAVING COUNT(*)>1)) u6
GROUP BY u6.sessionKey,u6.wxOpenid HAVING COUNT(*)>1)