一条SQL删除重复记录,重复的只保留一条

 

情景:

  我们的数据库中可能会存在很多因各种原因而重复的记录,我们需要对这些重复的记录进行删除,每组组重复的记录只保留一条就行

例如我们有这么个表:两个框框都是有重复记录的,红框和绿框都只需要留下一条,其他的都干掉。

一条SQL删除重复记录,重复的只保留一条

 

 

前提:

  1:每天记录都要有一个唯一id

  2:每组重复的记录要有字段能进行分组,例如上面我们按name、class、age、score相同的就是一组。

数据宝贵,请先备份!!!

数据宝贵,请先备份!!!

数据宝贵,请先备份!!!

放上整条SQL:

DELETE FROM "t_hw_test_del" t3
WHERE 1 = 1 AND EXISTS (
    SELECT * FROM (
        SELECT
             "id","name","class","age","score"
        FROM
            "t_hw_test_del"
        WHERE 1 = 1
    )t1 LEFT JOIN (
        SELECT
            "MAX"("id") as "id","name","class","age","score"
        FROM
            "t_hw_test_del"
        WHERE 1 = 1
        GROUP BY "name","class","age","score"
    ) t2 ON t1."id" = t2."id" 
    AND t1."name" = t2."name" 
    AND t1."class" = t2."class" 
    AND t1."age" = t2."age" 
    AND t1."score" = t2."score"
    WHERE t2."id" is NULL AND t1."id" = t3."id"
) 

 

 

按步分析:

1:找到我们需要的记录,因为我们可能只是要处理某天的记录,所以要先筛选一下,我这里用1=1来代替

SELECT
    *
FROM
    "t_hw_test_del"
WHERE 1 = 1

 

一条SQL删除重复记录,重复的只保留一条

 

 

2:找到我们每一组要保留的记录 ,我这里选每组id最大的保留,其他的不要
SELECT
    "MAX"("id") as "id","name","class","age","score"
FROM
    "t_hw_test_del"
WHERE 1 = 1
GROUP BY "name","class","age","score"

一条SQL删除重复记录,重复的只保留一条

 

 

3:用我们找到的需要处理的记录和要保留的记录关联起来, 用分组字段和唯一id关联,左联

SELECT * FROM (
    SELECT
         "id","name","class","age","score"
    FROM
        "t_hw_test_del"
    WHERE 1 = 1
)t1 LEFT JOIN (
    SELECT
        "MAX"("id") as "id","name","class","age","score"
    FROM
        "t_hw_test_del"
    WHERE 1 = 1
    GROUP BY "name","class","age","score"
) t2 ON t1."id" = t2."id" 
AND t1."name" = t2."name" 
AND t1."class" = t2."class" 
AND t1."age" = t2."age" 
AND t1."score" = t2."score"

一条SQL删除重复记录,重复的只保留一条

 

 

4:右表为null的记录,对应的左表记录就是我们要删掉的,加个条件右表为null

SELECT * FROM (
    SELECT
         "id","name","class","age","score"
    FROM
        "t_hw_test_del"
    WHERE 1 = 1
)t1 LEFT JOIN (
    SELECT
        "MAX"("id") as "id","name","class","age","score"
    FROM
        "t_hw_test_del"
    WHERE 1 = 1
    GROUP BY "name","class","age","score"
) t2 ON t1."id" = t2."id" 
AND t1."name" = t2."name" 
AND t1."class" = t2."class" 
AND t1."age" = t2."age" 
AND t1."score" = t2."score"
WHERE t2."id" is NULL

 

一条SQL删除重复记录,重复的只保留一条

 

5: 用我们原来的记录id在我们不需要的记录里面找,如果这条记录在我们不需要的记录集里,那这条记录就可以删除

DELETE FROM "t_hw_test_del" t3
WHERE 1 = 1 AND EXISTS (
    SELECT * FROM (
        SELECT
             "id","name","class","age","score"
        FROM
            "t_hw_test_del"
        WHERE 1 = 1
    )t1 LEFT JOIN (
        SELECT
            "MAX"("id") as "id","name","class","age","score"
        FROM
            "t_hw_test_del"
        WHERE 1 = 1
        GROUP BY "name","class","age","score"
    ) t2 ON t1."id" = t2."id" 
    AND t1."name" = t2."name" 
    AND t1."class" = t2."class" 
    AND t1."age" = t2."age" 
    AND t1."score" = t2."score"
    WHERE t2."id" is NULL AND t1."id" = t3."id"
) 

一条SQL删除重复记录,重复的只保留一条

 

 6: 检查一下结果符不符合我们的要求

SELECT
     "id","name","class","age","score"
FROM
    "t_hw_test_del"
WHERE 1 = 1 

一条SQL删除重复记录,重复的只保留一条

 

一条SQL删除重复记录,重复的只保留一条

上一篇:ASP.NET Core 使用 AutoFac 注入 DbContext


下一篇:JMeter-结合BeanShell生成MD5加密数据写入数据库