171 篇文章 41 订阅
27 篇文章 7 订阅

# 正文

## 场景一   单个字段重复数据查找 & 去重

 SELECT account ,COUNT(account) as count FROM accountinfo GROUP BY account;

第二步，利用having 拼接筛选条件，写出来的mysql 语句是：

 SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) > 1;


第三步

 SELECT * FROM  accountinfo WHERE account IN
(
SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) > 1
);


### 不，准确点应该说是，重复的数据都只留下一条即可，这才是去重。

SELECT min(id) as id from (

SELECT * FROM  accountinfo WHERE account IN
(
SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) > 1
)

) a GROUP BY a.account

SELECT t1.id FROM (

SELECT id  FROM accountinfo WHERE account IN (  SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) > 1) ) t1

WHERE t1.id NOT IN (

SELECT min(id) AS id FROM (

SELECT * FROM  accountinfo WHERE account IN
(
SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) > 1
)

) a GROUP BY a.account

)

DELETE FROM accountinfo WHERE id IN (

SELECT t1.id FROM (

SELECT id  FROM accountinfo WHERE account IN (  SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) > 1) ) t1

WHERE t1.id NOT IN (

SELECT min(id) AS id FROM (

SELECT * FROM  accountinfo WHERE account IN
(
SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) > 1
)

) a GROUP BY a.account

)

)


# 场景二  多个字段重复数据查找 & 去重

示例  accountinfo 表数据如下：

想要去重，我们的首要想到的也是先把 重复数据 找出来。

SELECT account, COUNT(account), deviceId, COUNT(deviceId)
FROM accountinfo
GROUP BY account, deviceId
HAVING  (COUNT(account) > 1) AND  (COUNT(deviceId) > 1) 

SELECT t.* FROM  accountinfo t, (

SELECT account, COUNT(account), deviceId, COUNT(deviceId)
FROM accountinfo
GROUP BY account, deviceId
HAVING  (COUNT(account) > 1) AND  (COUNT(deviceId) > 1)
) a

WHERE t.account=a.account AND t.deviceId=a.deviceId 

### 去重

account为D 且 deviceId 为 D444  二条重复数据里面，保留 id最小的 等于40 这一条.

SELECT min(id) as id FROM (

SELECT t.* FROM  accountinfo t, (

SELECT account, COUNT(account), deviceId, COUNT(deviceId)
FROM accountinfo
GROUP BY account, deviceId
HAVING  (COUNT(account) > 1) AND  (COUNT(deviceId) > 1)
) a

WHERE t.account=a.account AND t.deviceId=a.deviceId

)a  GROUP BY a.account,a.deviceId


SELECT t.* FROM  accountinfo t, (

SELECT account, COUNT(account), deviceId, COUNT(deviceId)
FROM accountinfo
GROUP BY account, deviceId
HAVING  (COUNT(account) > 1) AND  (COUNT(deviceId) > 1)
) a

WHERE t.account=a.account AND t.deviceId=a.deviceId

) b   WHERE b.id

NOT IN (

SELECT min(id) as id FROM (

SELECT t.* FROM  accountinfo t, (

SELECT account, COUNT(account), deviceId, COUNT(deviceId)
FROM accountinfo
GROUP BY account, deviceId
HAVING  (COUNT(account) > 1) AND  (COUNT(deviceId) > 1)
) a

WHERE t.account=a.account AND t.deviceId=a.deviceId

)a  GROUP BY a.account,a.deviceId

)



DELETE FROM accountinfo WHERE id in (

SELECT b.id  FROM  (

SELECT t.* FROM accountinfo  t, (

SELECT account, COUNT(account), deviceId, COUNT(deviceId)
FROM accountinfo
GROUP BY account, deviceId
HAVING  (COUNT(account) > 1) AND  (COUNT(deviceId) > 1)
) a

WHERE t.account=a.account AND t.deviceId=a.deviceId

) b   WHERE b.id

NOT IN (

SELECT min(id) as id FROM (

SELECT t.* FROM  accountinfo t, (

SELECT account, COUNT(account), deviceId, COUNT(deviceId)
FROM accountinfo
GROUP BY account, deviceId
HAVING  (COUNT(account) > 1) AND  (COUNT(deviceId) > 1)
) a

WHERE t.account=a.account AND t.deviceId=a.deviceId

)a  GROUP BY a.account,a.deviceId

)

)


• 47
点赞
• 162
收藏
• 打赏
• 11
评论
07-13 2420
10-08
10-24 585
01-20 3607
07-25 788
10-29 5980
04-08 3688
05-11 3192
09-07 2万+
06-24 4186

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

¥2 ¥4 ¥6 ¥10 ¥20

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。