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

Question

我想删除表中的重复数据。但我的 sql 脚本返回查询错误 (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

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

第一个声明

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 ;

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

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;

Example on db-fiddle.com

2。创建主键

您的问题是建议始终定义主键的原因之一。好消息 - 创建 AUTO_INCRMENT 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
; 

Example on 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;

Example on db-fiddle.com