1 SQL去重

SQL中去除完全相同數(shù)據(jù)可以用distinct關(guān)鍵字，任意字段去重可以用group by，以下面的數(shù)據(jù)表為例。

2 distinct

存在兩條完全相同的紀(jì)錄，用關(guān)鍵字distinct就可以去掉

根據(jù)單個(gè)字段去重，能精確去重;

作用在多個(gè)字段時(shí)，只有當(dāng)這幾個(gè)字段的完全相同時(shí)，才能去重;

關(guān)鍵字distinct只能放在SQL語句中的第一個(gè)，才會(huì)起作用

一般用來返回不重復(fù)的記錄條數(shù)，返回不重復(fù)的條數(shù)(去掉test重復(fù)的，就剩下6條)

3 group by

1. 查詢根據(jù)名字去重后數(shù)據(jù)(名字相同取id值大的)

SELECT * FROM stu WHERE id IN (SELECT MAX(id) FROM stu GROUP BY `name`)

2. 刪除名字相同數(shù)據(jù)(名字相同保留id值大的)

group by + count + max去掉重復(fù)數(shù)據(jù)

1）SELECT * FROM stu

2）加上group by 后，會(huì)將重復(fù)的數(shù)據(jù)去掉了

3) 條件(名字)是數(shù)量大于1的重復(fù)數(shù)據(jù)

SELECT `name` FROM stu GROUP BY `name` HAVING COUNT(`name`) > 1

#條件是數(shù)量大于1的重復(fù)數(shù)據(jù)
SELECT * FROM stu WHERE `name` IN(
SELECT `name` FROM stu GROUP BY `name` HAVING COUNT(`name`)>1 
)

4）查看某字段重復(fù)數(shù)據(jù)的id

SELECT id, COUNT(*) FROM stu 
GROUP BY NAME DESC HAVING(COUNT(*) > 0)

5）查詢所有重復(fù)數(shù)據(jù)

SELECT * FROM stu WHERE NAME IN (SELECT `name` FROM stu GROUP BY `name` HAVING COUNT(`name`) > 1)

5) 去重

可以使用distinct去重（返回不重復(fù)的用戶名）

刪除多余的重復(fù)記錄（name），只保留id最大的記錄。

DELETE FROM stu 
WHERE id NOT IN ( SELECT a.id FROM ( SELECT MAX( id ) AS id FROM stu GROUP BY `name` )a )

或者

 DELETE FROM stu WHERE `name` IN (SELECT `name` FROM (SELECT `name` FROM stu GROUP BY `name` HAVING COUNT(`name`)>1) e)
 AND id NOT IN (SELECT id FROM (SELECT MAX(id) AS id FROM stu GROUP BY `name` HAVING COUNT(`name`)>1) t)
 #查詢顯示重復(fù)的數(shù)據(jù)都是顯示最前面的幾條，因此不需要查詢是否最小值

錯(cuò)誤刪除

DELETE FROM stu WHERE name IN (SELECT name FROM stu GROUP BY name HAVING COUNT(name)>1)
AND id NOT IN (SELECT MAX(id) FROM stu GROUP BY stu HAVING COUNT(name)>1)

原因是：不能將直接查處來的數(shù)據(jù)當(dāng)做刪除數(shù)據(jù)的條件，我們應(yīng)該先把查出來的數(shù)據(jù)新建一個(gè)臨時(shí)表，然后再把臨時(shí)表作為條件進(jìn)行刪除功能

4 總結(jié)

去重后名字記錄

SELECT `name` FROM stu 
GROUP BY NAME HAVING(COUNT(*) > 0)

2）

所有重復(fù)名字的記錄

SELECT `name` FROM stu 
GROUP BY NAME HAVING COUNT(*) > 1

3）把所有重復(fù)的記錄都刪了

DELETE FROM stu WHERE name IN
(SELECT name FROM stu GROUP BY name HAVING COUNT(*)>1)

無法在刪除時(shí)同時(shí)查詢這張表，這個(gè)問題只在MySQL中出現(xiàn)，oracle沒有。怎么解決？我們只需要在查出結(jié)果以后加一張中間表。讓執(zhí)行器認(rèn)為我們要查的數(shù)據(jù)不是來自正在刪的這張表就可以了。

DELETE FROM stu WHERE `name` IN 
    (SELECT a.name FROM 
        (SELECT `name` FROM stu GROUP BY `name` HAVING COUNT(*)>1) a)

所有重復(fù)數(shù)據(jù)都刪除, 就剩王五一條數(shù)據(jù)了

4) 現(xiàn)在刪除所有重復(fù)數(shù)據(jù)數(shù)據(jù)做完了，考慮怎么保留重復(fù)數(shù)據(jù)中id最小的。只需要在刪除時(shí)讓刪除該條的記錄id不在重復(fù)數(shù)據(jù)id最小的當(dāng)中就可以了。

DELETE FROM stu WHERE `name` IN 
    (SELECT a.name FROM 
        (SELECT `name` FROM stu GROUP BY `name` HAVING COUNT(*)>1) a)
     AND id NOT IN 
    (SELECT b.id FROM 
        (SELECT MIN(id) id FROM stu 
            GROUP BY `name` HAVING COUNT(*)>1) b);

還有簡(jiǎn)單辦法 算出去重后所有數(shù)據(jù)（保留最小ID），然后刪除id不在該數(shù)組里的

 DELETE FROM stu WHERE id NOT IN (SELECT t.id FROM (SELECT MIN(id) AS id FROM stu GROUP BY `name`)t)

到此這篇關(guān)于SQL刪除重復(fù)數(shù)據(jù)的文章就介紹到這了,更多相關(guān)SQL刪除重復(fù)數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論