从mysql表删除重复数据日期

Question

如何从MySQL表中删除昨天的重复数据并保留最新记录？

例如，使用以下数据：

SELECT * FROM data;

| pid | serviceid | id             | created
| ----| ----------|----------------|---------
| 10  | ABNCKN    | Q0CHZ2GI1VKOPL | 2022-06-14 10:49:34
| 11  | ABNCKN    | Q0CHZ2GI1VKOPL | 2022-06-14 10:49:34
| 12  | KSPSLS    | QLSPDF0S0SDFKK | 2022-06-15 11:44:21
| 13  | AKNKSL    | QLSPDF0S0SDFKK | 2022-06-15 12:51:42
| 14  | AKNKSL    | QLSPDF0S0SDFKK | 2022-06-15 12:51:42

我使用

DELETE n1 FROM data n1, data n2 WHERE n1.pid < n2.pid AND n1.id = n2.id

如何删除仅删除重复项并保留昨天数据的最新PID？

预期的输出将被

SELECT * FROM data;

| pid | serviceid | id             | created
| ----| ----------|----------------|---------
| 10  | ABNCKN    | Q0CHZ2GI1VKOPL | 2022-06-14 10:49:34
| 11  | ABNCKN    | Q0CHZ2GI1VKOPL | 2022-06-14 10:49:34
| 12  | KSPSLS    | QLSPDF0S0SDFKK | 2022-06-15 11:44:21
| 14  | AKNKSL    | QLSPDF0S0SDFKK | 2022-06-15 12:51:42

删除 | 13 | aknksl | QLSPDF0S0SDFKK | 2022-06-15 12:51:42

Answer 1

DELETE * FROM data WHERE id IN (SELECT id FROM data WHERE name="ABNCKN" LIMIT((SELECT COUNT(name) FROM data WHERE name="ABNCKN") - 1)));

从数据中选择ID name =“ abnckn” ＆gt;＆gt;此查询将获取具有特定名称的所有ID，并且限制将限制返回的行数。除一行外，基于重复的行，行的数量将是动态的。您可以将订单添加到查询中以排列它们。

最后删除除一行以外的所有重复行。

Answer 2

不太了解为什么您将一些重复的数据保留在预期的输出中，但是您可以尝试的是使用Join获得最后一个ID，并从Where so so中删除它：

DELETE d1 FROM data d1, data d2
JOIN (SELECT MAX(pid) pid FROM data) d3
WHERE d1.pid != d3.pid AND d1.pid < d2.pid AND d1.id = d2.id;

Answer 3

您可以使用自连接并将条件添加到仅删除昨天的数据：

DELETE t1 FROM data t1 
  INNER JOIN data t2 
  ON t1.pid < t2.pid 
    AND t1.serviceid = t2.serviceid 
    AND t1.id = t2.id
    AND t1.created = t2.created
  WHERE CAST(t1.created AS DATE) = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY);
    
SELECT * FROM data;

请参阅此

对于mysql 8+，您也可以使用 row_number（）：

WITH CTE AS
(
  SELECT *, ROW_NUMBER() OVER(PARTITION BY serviceid,id,created ORDER BY pid DESC) AS RN
  FROM data
  WHERE CAST(created AS DATE) = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
)
DELETE data
FROM data
JOIN CTE ON data.pid=CTE.pid
WHERE CTE.RN>1;

SELECT * FROM data;

检查此

L

2022-06-18	​	​	​
​	​	​	​
​	​	​	​07:27:35
12	kspsls	qlspdf0s0sdfkk	2022-06-19 07:27:35
14	AKNKSL	QLSPDF0S0SDFKK	2022-06-19 07:27:35