删除数据库中重复数据的几个方法

在数据库中删除重复数据的常用方法主要有以下几种:

1. 创建唯一索引,再删除重复键值的数据。这种方法需要先创建一个唯一索引,以标识出重复数据。然后通过检索索引并删除重复记录来清除重复数据。比如:

CREATE UNIQUE INDEX idx_unique ON table_name(column_name);
DELETE t1 
FROM table_name t1
    INNER JOIN table_name t2
    WHERE t1.column_name = t2.column_name
    AND t1.id > t2.id;

2. 使用GROUP BY和HAVING语句进行删除。这种方法会检索出重复的值,然后仅保留一条数据。SQL如下:

DELETE FROM table_name 
WHERE id IN (
    SELECT id 
    FROM table_name
    GROUP BY column_name
    HAVING COUNT(*) > 1
);

3. first_value和last_value窗口函数。该方法会对表进行分区,然后删除除了该分区第一条和最后一条数据外的所有重复数据记录。SQL如下:

DELETE FROM table_name 
WHERE id NOT IN (
    SELECT FIRST_VALUE(id) OVER(PARTITION BY column_name ORDER BY id) AS first_id 
    FROM table_name
) 
AND id NOT IN (
    SELECT LAST_VALUE(id) OVER(PARTITION BY column_name ORDER BY id 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_id
    FROM table_name
);

4. 使用CTE或子查询进行删除。这种方法通过CTE或子查询来选取要保留的数据,然后删除表中除这些数据外的所有行。SQL如下:

WITH cte AS (
    SELECT MIN(id) AS id FROM table_name GROUP BY column_name
)
DELETE FROM table_name 
WHERE id NOT IN (SELECT id FROM cte);

以上是删除数据库中重复数据常用的几种方法,可以根据具体情况选择使用。