mysql - 删除重复号码状态存在吗？

Question

如何将重复项删除到新表中？

例如表中:

id |   number      |    status    | statusdate 
1  | 12121312      |   Processing | 01/06/2015  12:32:00
2  | 12121312      |   Processing | 02/06/2015  13:37:00
3  | 12121312      |   Processing | 03/06/2015  14:29:00
4  | 99999999      |   Processing | 03/06/2015  15:33:00
5  | 99999999      |   Completed  | 03/06/2015  18:39:00
6  | 99999999      |   Completed  | 04/06/2015  19:39:00

1. 从 id 1 到 3 有重复的数字，但没有“已完成”状态，所以从最早的日期开始获取数字

2. 从id 4到6，有重复的数字，但“已完成”状态确实存在。获取最新日期的号码

在新表中，应该是这样的:

id |   number      |    status    | statusdate 
1  | 12121312      |   Processing | 01/06/2015  12:32:00
6  | 99999999      |   Completed  | 04/06/2015  19:39:00

如何做到这一点？

Answer 1

这是一个未优化的版本:

DELETE FROM table_name as T
WHERE ( 
        number in (SELECT number
                   FROM table_name
                   WHERE status = 'Completed')
        AND statusdate < (SELECT MAX(statusdate)
                          FROM table_name
                          GROUP BY number
                          HAVING number = T.number)
    )
    OR
    (
        number in (SELECT number
                   FROM table_name
                   WHERE status = 'Processing')
        AND statusdate > (SELECT MIN(statusdate)
                          FROM table_name
                          GROUP BY number
                          HAVING number = T.number)                   
    );

这些是它删除的记录:

id  number      status      statusdate
2   12121312    Processing  February, 06 2015 13:37:00
3   12121312    Processing  March, 06 2015 14:29:00
4   99999999    Processing  March, 06 2015 15:33:00
5   99999999    Completed   March, 06 2015 18:39:00
6   99999999    Completed   April, 06 2015 19:39:00

只需将 Fiddle 中的 SELECT * FROM 替换为 DELETE FROM .