mysql - 使用 mysql 选择和删除组内的行

Question

我看过重复行操作的示例，但我不知道如何映射它们来解决我的问题。

+----+------------+------+---------+
| id | date       | file | status  |
+----+------------+------+---------+
|  1 | 2011-12-01 |    1 | Pending |
|  2 | 2011-12-02 |    1 | Pending |
|  3 | 2011-12-03 |    1 | Done    |
|  4 | 2011-12-04 |    1 | Pending |
|  5 | 2011-12-05 |    1 | Done    |
|  6 | 2011-12-06 |    1 | Pending |
|  7 | 2011-12-07 |    1 | Pending |
|  8 | 2011-12-08 |    1 | Pending |
|  9 | 2011-12-09 |    2 | Pending |
| 10 | 2011-12-10 |    2 | Pending |
| 11 | 2011-12-11 |    3 | Pending |
| 12 | 2011-12-12 |    4 | Done    |
| 13 | 2011-12-13 |    5 | Pending |
| 14 | 2011-12-14 |    5 | Done    |
| 15 | 2011-12-15 |    5 | Pending |
+----+------------+------+---------+

对于表中的每个文件:

1. 我需要先选择/删除 status='Pending' 的任何行，并且其日期早于 status='Done' 的任何行的最新日期。例如，这将选择/删除 ID 为 1、2、4 和 13 的行。

2. 接下来我需要选择/删除任何状态为“待处理”的行，并且它不是状态为“待处理”的最早日期。例如，这将选择/删除 ID 为 7、8 和 10 的行。

结果表是:

+----+------------+------+---------+
| id | date       | file | status  |
+----+------------+------+---------+
|  3 | 2011-12-03 |    1 | Done    |
|  5 | 2011-12-05 |    1 | Done    |
|  6 | 2011-12-06 |    1 | Pending |
|  9 | 2011-12-09 |    2 | Pending |
| 11 | 2011-12-11 |    3 | Pending |
| 12 | 2011-12-12 |    4 | Done    |
| 14 | 2011-12-14 |    5 | Done    |
| 15 | 2011-12-15 |    5 | Pending |
+----+------------+------+---------+

---

这将在 MySQL 中创建并填充测试表:

创建表 测试 ( id int(11) NOT NULL AUTO_INCREMENT, date 日期默认为空， 文件 int(11) 默认为空， status varchar(45) 默认 NULL， 主键(id)) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

INSERT INTO test VALUES (1,'2011-12-01',1,'Pending'),(2,'2011-12-02',1,'Pending'), (3,'2011-12-03',1,'完成'),(4,'2011-12-04',1,'待定'),(5,'2011-12-05',1,'完成'),(6,'2011-12-06',1,'待定'),(7,'2011-12-07',1,'待定'),(8,'2011-12-08' ,1,'待定'),(9,'2011-12-09',2,'待定'),(10,'2011-12-10',2,'待定'),(11,'2011- 12-11',3,'待定'),(12,'2011-12-12',4,'完成'),(13,'2011-12-13',5,'待定'),(14 ,'2011-12-14',5,'完成'),(15,'2011-12-15',5,'待定');

---

感谢 ziesemer 提供了正确的 SELECT 查询——我从他们那里学到了很多东西。不幸的是，MySQL 似乎不允许使用子查询进行 DELETE，因此我将 ziesemer 的答案转换为使用 JOINS。但我是一个 SQL 菜鸟，所以如果这些可以改进，请更正:

SELECT DISTINCT t1.* FROM test t1 INNER JOIN test t2
 WHERE t1.file = t2.file
   AND t1.status = 'Pending'
   AND t2.status = 'Done'
   AND t1.date < t2.date;

SELECT DISTINCT t1.* FROM test t1 INNER JOIN test t2
 WHERE t1.file = t2.file
   AND t1.status = 'Pending'
   AND t2.status = 'Pending'
   AND t1.date > t2.date;

要删除，请将 SELECT 行替换为:

DELETE t1 FROM test t1 INNER JOIN test t2

Answer 1

我对这些进行了测试，独立工作 - 尽管必须在第 1 个之后执行第 2 个才能获得您在示例中提供的结果。我很难让它们作为一个 Select 工作，因为第二个查询取决于第一个查询完成后表的状态...

Select *
    From my_table t1
    Where (status = 'Pending'
        And date < (
            Select Max(date)
                From my_table t2
                Where t2.file = t1.file
                    And t2.status = 'Done'));

Select *
    From my_table t1
    Where (status = 'Pending'
        And date > (
            Select Min(date)
                From my_table t2
                Where t2.file = t1.file
                    And t2.status = 'Pending'));

(我会给其他任何回答可以在一个查询中做到这一点，同时产生相同的、准确的结果的人 +1 - 我现在很难过。)