mysql - 如何删除单个表中具有重复 row_id 但日期字段值不同的行？

Question

mysql - 如何在单个表中删除具有重复“row_id”但具有不同“recd_date”字段值的行，只留下具有最​​新“recd_date”的行？

试过下面的脚本...

DELETE FROM table1 
WHERE table1.id = table1.id 
AND table1.recd_date < table1.recd_date;

...但是它删除了表中的所有记录，从逻辑上讲，我可以理解。但我觉得应该有一个非常简单直接的解决方案。

下图是现有内容和预期结果的示例。

现有表数据的例子

doc_id recd_date  recd_amount update_date
    72 2015-01-01        6000 2015-04-30
    72 2015-01-01        6000 2015-02-30
    12 2013-02-01        5000 2015-08-31
    12 2013-02-01        5000 2014-08-31
    12 2013-02-01        5000 2013-06-30

期望结果的例子

doc_id recd_date  recd_amount update_date
    72 2015-01-01        6000 2015-04-30
    12 2013-02-01        5000 2015-08-31

Answer 1

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(doc_id INT NOT NULL
,recd_date DATE NOT NULL
,recd_amount INT NOT NULL
,update_date DATE NOT NULL
,PRIMARY KEY(doc_id,update_date)
);

INSERT INTO my_table VALUES
(72,'2015-01-01',6000,'2015-04-30'),
(72,'2015-01-01',6000,'2015-02-28'),
(12,'2013-02-01',5000,'2015-08-31'),
(12,'2013-02-01',5000,'2014-08-31'),
(12,'2013-02-01',5000,'2013-06-30');

SELECT * 
  FROM my_table x 
  LEFT 
  JOIN  
     ( SELECT doc_id
            , MAX(update_date) max_update_date 
         FROM my_table 
        GROUP 
           BY doc_id
     ) y 
    ON y.doc_id = x.doc_id 
   AND y.max_update_date = x.update_date;

+--------+------------+-------------+-------------+--------+-----------------+
| doc_id | recd_date  | recd_amount | update_date | doc_id | max_update_date |
+--------+------------+-------------+-------------+--------+-----------------+
|     12 | 2013-02-01 |        5000 | 2013-06-30  |   NULL | NULL            |
|     12 | 2013-02-01 |        5000 | 2014-08-31  |   NULL | NULL            |
|     12 | 2013-02-01 |        5000 | 2015-08-31  |     12 | 2015-08-31      |
|     72 | 2015-01-01 |        6000 | 2015-02-28  |   NULL | NULL            |
|     72 | 2015-01-01 |        6000 | 2015-04-30  |     72 | 2015-04-30      |
+--------+------------+-------------+-------------+--------+-----------------+

DELETE x
  FROM my_table x 
  LEFT 
  JOIN  
     ( SELECT doc_id
            , MAX(update_date) max_update_date 
         FROM my_table 
        GROUP 
           BY doc_id
     ) y 
    ON y.doc_id = x.doc_id 
   AND y.max_update_date = x.update_date
 WHERE y.doc_id IS NULL;
Query OK, 3 rows affected (0.00 sec)

SELECT * FROM my_table;
+--------+------------+-------------+-------------+
| doc_id | recd_date  | recd_amount | update_date |
+--------+------------+-------------+-------------+
|     12 | 2013-02-01 |        5000 | 2015-08-31  |
|     72 | 2015-01-01 |        6000 | 2015-04-30  |
+--------+------------+-------------+-------------+

http://sqlfiddle.com/#!9/fca88/1