前文
重複資料的情況分為兩種:
單個字段 資料重複
多個字段 資料重複
是以該篇的内容就是包括
單個字段的重複資料查找 與 去重
多個字段的重複資料查找 與 去重
正文
示例 accountinfo 表資料如下:
場景一 單個字段重複資料查找 & 去重
我們要把上面這個表中 單個字段 account字段相同的資料找出來。
思路 分三步 簡述:
第一步
要找出重複資料,我們首先想到的就是,既然是重複,那麼 數量就是大于 1 就算是重複。 那就是 count 函數 。
因為我們要排查的是 單個 字段account ,那麼就是需要按照 account 字段 次元 去分組。 那就是 group by
那麼我們第一步寫出來的mysql 語句是:
SELECT account ,COUNT(account) as count FROM accountinfo GROUP BY account;
查詢結果如下:
第二步
沒錯,如我們所想,count大于1的即是 account為 A 和 B 的資料。
那麼我們稍作篩選,隻把count大于1的資料的account 找出來。
having
SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) > 1;
查詢結果如下:
第三步
重複的account資料 A B 都找出來了,接下來我們隻需要把account為A 和 B 的其他資料都一起查詢出來。
那就是利用第二步查出來的資料做為子查詢條件,使用 IN 函數。
第三步寫出來的mysql 語句是:
SELECT * FROM accountinfo WHERE account IN
(
SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) > 1
);
查詢結果如下:
可以看到重複的資料都被我們篩選出來了。
那麼怎麼去重呢? 也就是說,把重複的資料删掉。
不,準确點應該說是,重複的資料都隻留下一條即可,這才是去重。
緊接着上面,畢竟去重的前提肯定是找重,上面我們已經實作找重了,是以緊接着進行去重。
按照我們步驟三得到的圖,就是我們需要删掉一條account為A的資料, 一條account為B的資料,去重結果圖應該如下:
思路也分三步簡述:
第一步
删掉資料,我們一般選擇接住主鍵來删除,是以我們考慮從id入手。
再來看我們的重複資料:
如果我們想在這些重複資料裡面,每個都保留一條,如 account 為 A 中 id=10 的資料, account 為B 中 id=20 的資料 ,那麼第一步我們就得把這兩條資料從上面的
最終得到的資料 篩選出來,也就是按照 account分組,保留裡面id最小的資料。使用 group by 和 min
是以去重我們第一步寫出來的mysql 語句是:
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
查詢結果如下:
第二步
想要保留的資料已經找出來了,那麼接下來就是從所有的重複資料裡面 删掉 不為 我們保留的資料 即可 。
那麼就是找出我們需要删除的id, 使用 not in
是以去重我們第二步寫出來的mysql 語句是:
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
)
查詢的結果為:
對這個sql語句稍作文字說明:
第三步,删掉第二步找出來的資料即可,根據id删除。
是以第三步寫出來的mysql語句是:
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 表資料如下:
想要去重,我們的首要想到的也是先把 重複資料 找出來。
現在的重複定義是, account 和 deviceId 都相同的 時候,這種資料就是重複資料(也就是上圖綠色框出來的就是同時多個字段都存在重複的資料)。
思路簡述:
第一步
因為有了文章上半部講到的單個字段重複的資料查找思路,是以到這邊應該更好了解了。
同樣, account 和 deviceId 都相同的重複資料就是指, 這種資料存在的數量 大于 2,那麼就是存在重複了。
我們還是使用到了 group by 函數 和 count 函數 和 having and 函數(因為需要同時滿足兩個字段條件,使用and)。
第一步寫出來的mysql語句是:
SELECT account, COUNT(account), deviceId, COUNT(deviceId)
FROM accountinfo
GROUP BY account, deviceId
HAVING (COUNT(account) > 1) AND (COUNT(deviceId) > 1)
查詢出來的結果如下圖:
第二步
一樣 也是把第一步裡的到的關鍵資訊 account 和 deviceId做為子查詢條件,從原表裡把 account 和 deviceId 同時相同的資料都查找出來。
第二步寫出來的mysql語句是:
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 和 deviceId 同時都相同的資料!
去重
第一步
思路一樣,我們對于重複的資料每個隻保留一條,那就是把這些涉及重複的資料每個都找出一條,也是根據id入手。
也就是在account為 C 且 deviceId 為 C333 三條重複資料裡面,保留 id最小的 等于30 這一條;
account為D 且 deviceId 為 D444 二條重複資料裡面,保留 id最小的 等于40 這一條.
是以第一步我們先寫出來的mysql語句為:
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
查詢結果如下圖:
第二步
就是從涉及重複資料裡面,找出除了我們需要保留的id之外的那批id, 那就是需要我們執行删除的資料id。
是以第二步我們先寫出來的mysql語句為:
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
)
查詢出來的結果為:
那麼最後删掉這些id的資料即可:
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
)
)
去重後結果為: