mysql - 如何使用cte删除mysql中的重复数据

Question

我想删除表中的重复数据。但是我的 sql sript 在查询 (1064) 中返回错误。

我在我的 MySQL 服务器上尝试了两个单独的 sql 语句，但它一直抛出语法错误。

表列：

msisdn_lte
batch_id
file_id
date_key
call_timestamp
data_volume
da_value_before_call
da_value_after_call
served_account
source_file_nm
quote
pull_date_time

所有具有完全相同数据的插入 2 次。IE 每个不同的数据都有两倍

第一个声明

DELETE A
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY * ORDER BY call_timestamp) AS rn
FROM broadband_usage_history
) A
WHERE A.rn > 1;

第二个声明

WITH usage_cte AS 
(
    SELECT *, ROW_NUMBER() Over(Partition  BY call_timestamp order by call_timestamp) AS row_number
    FROM broadband_usage_history
) 
SELECT * FROM usage_cte ;

我希望获得表中每个数据的一个输入，但我什至无法运行查询。

标签： mysqlcommon-table-expressionwindow-functions

Answer 1

如果您可以在所有列中拥有具有相同数据的重复行，则无法删除行而不删除其重复项。在这种情况下，CTE 和 ROW_NUMBER() 对您没有帮助，因为您无法从 MySQL 中的 CTE 中删除。带有 CTE 的 JOIN 也无济于事，因为您没有用于 ON 子句的标识列。我看到两种方法可以解决您的问题：

1.创建表副本

创建具有相同架构的新表并仅复制不同的数据。然后删除原始表并重命名新表以替换它：

CREATE TABLE broadband_usage_history_distinct LIKE broadband_usage_history;

INSERT INTO broadband_usage_history_distinct
    SELECT DISTINCT * FROM broadband_usage_history;

DROP TABLE broadband_usage_history;

RENAME TABLE broadband_usage_history_distinct TO broadband_usage_history;

db-fiddle.com 上的示例

2. 创建主键

您的问题是原因之一，为什么建议始终定义主键。好消息 - 创建 AUTO_INCREMENT PRIMARY KEY 永远不会太晚。之后，您可以将其用作 DELETE-JOIN 查询的行标识符：

ALTER TABLE broadband_usage_history
  ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;

WITH cte AS (
  SELECT id, ROW_NUMBER() OVER (
    PARTITION BY
      msisdn_lte,
      batch_id,
      file_id,
      date_key,
      call_timestamp,
      data_volume,
      da_value_before_call,
      da_value_after_call,
      served_account,
      source_file_nm,
      quote,
      pull_date_time
    ORDER BY id
  ) AS rn
  FROM broadband_usage_history
)
  DELETE t
  FROM cte
  JOIN broadband_usage_history t USING(id)
  WHERE rn > 1
; 

db-fiddle.com 上的示例

您还可以使用 GROUP BY 子查询删除没有窗口函数的旧版本中的重复项：

ALTER TABLE broadband_usage_history
  ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;

DELETE t
FROM broadband_usage_history t
LEFT JOIN (
  SELECT MIN(id) AS id
  FROM broadband_usage_history
  GROUP BY 
      msisdn_lte,
      batch_id,
      file_id,
      date_key,
      call_timestamp,
      data_volume,
      da_value_before_call,
      da_value_after_call,
      served_account,
      source_file_nm,
      quote,
      pull_date_time
) x USING (id)
WHERE x.id IS NULL;

db-fiddle.com 上的示例