使用mysql选择和删除组内的行 [英] select and delete rows within groups using mysql

问题描述

我已经看到了重复行操作的例子，但我无法弄清楚如何映射它们来解决我的问题。

  + ---- + ------------ + ------ + --------- + 
 | id |日期|文件|状态| 
 + ---- + ------------ + ------ + --------- + 
 | 1 | 2011-12-01 | 1 |待处理| 
 | 2 | 2011-12-02 | 1 |待处理| 
 | 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 |待处理| 
 + ---- + ------------ + ------ + --------- + 
  
 
 
 
对于表格中的每个文件： 
 
 
 
 
 
我需要先选择/删除状态为'待定'的任何行，并且其日期比状态='完成'的任何行的最新日期还早。例如，这将选择/删除id为1,2,4和13的行。
  
 
 
我需要接下来选择/删除任何行，其中状态='待定'，并不是状态='待定'的最早日期。例如，这将选择/删除ID为7,8和10的行。
  
 
结果表格为：
  + ---- + ------------ + ------ + --------- + 
 | id |日期|文件|状态| 
 + ---- + ------------ + ------ + --------- + 
 | 3 | 2011-12-03 | 1 |完成| 
 | 5 | 2011-12-05 | 1 |完成| 
 | 6 | 2011-12-06 | 1 |待处理| 
 | 9 | 2011-12-09 | 2 |待处理| 
 | 11 | 2011-12-11 | 3 |待处理| 
 | 12 | 2011-12-12 | 4 |完成| 
 | 14 | 2011-12-14 | 5 |完成| 
 | 15 | 2011-12-15 | 5 |待处理| 
 + ---- + ------------ + ------ + --------- + 
  
 p 
 
 
 
 
 
这将在MySQL中创建和填充测试表：
 
 
 
 CREATE TABLE  test （
  id  int（11）NOT NULL AUTO_INCREMENT，
  date  date DEFAULT NULL，
  file  int（11）DEFAULT NULL，
  status  varchar（45）DEFAULT NULL，
 PRIMARY KEY（ id $ / code>）
）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）'，1，'Pending'），（5，'2011-12-05'，1，'Done'），（6，'2011-12-06'，1，'Pending'），（ 7，'2011-12-07'，1，'待定'），（8，'2011-12-08'，1，'待定'），（9，'2011-12-09'，2，'待定'），（10，'2011-12-10'，2，'Pending'），（11，'2011-12-11'，3，'Pending'），（12，'2011-12-12'， 4， '完成'）， （13，'2011-12-13'，5，'待定'），（14，'2011-12-14'，5，'完成'），（15，'2011-12-15'，5，' Pending'）; 
 
 
 
 
 
感谢ziesemer对于正确的SELECT查询 - 我从他们身上学到了很多东西。不幸的是，似乎MySQL不允许DELETE子查询，所以我将ziesemer的答案转换为使用JOINS。但是我是一个SQL noob，所以请更正，如果这些可以改进：
  SELECT DISTINCT t1。* FROM test t1 INNER JOIN测试t2 
 WHERE t1.file = t2.file 
 AND t1.status ='待处理'
 AND t2.status ='完成'
 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 ='待定'
和t1.date> t2.date; 
  
要删除，请将SELECT行替换为：
  DELETE t1 FROM test t1 INNER JOIN test t2 
  
 
 
解决方案
我有这些测试，独立工作 - 尽管第二个必须在第一个之后执行以获得您在示例中提供的结果。我有一些困难，让他们作为一个选择工作，因为第二个查询依赖于第一个完成后的表的状态... 
 选择* 
从my_table t1 
其中（status ='Pending'
和日期<（
选择最大值（日期）
从my_table t2 
其中t2.file = t1.file 
和t2.status ='完成'））; 
 
选择* 
从my_table t1 
其中（status ='Pending'
和日期>（
选择Min（日期）
从my_table t2 
其中t2.file = t1.file 
和t2.status ='Pending'））; 
  
（我会给任何其他人的+1提供一个查询的答案，产生相同的，准确的结果 - 我现在被困住了。）
 
I've seen examples for duplicate row manipulation, but I can't figure out how to map them to solve my problem.
+----+------------+------+---------+
| 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 |
+----+------------+------+---------+
For each file in the table:

I need to first select/delete any row where status='Pending', and its date is older than the youngest date for any row where status='Done'.  For the example, this would select/delete rows with id 1, 2, 4, and 13.
I need to next select/delete any row where status='Pending' and it's not the oldest date where status='Pending'.  For the example, this would select/delete rows with id 7, 8, and 10.
The resulting table is:
+----+------------+------+---------+
| 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 |
+----+------------+------+---------+




This will create and populate the test table in MySQL:

CREATE TABLE test (
  id int(11) NOT NULL AUTO_INCREMENT,
  date date DEFAULT NULL,
  file int(11) DEFAULT NULL,
  status varchar(45) DEFAULT NULL,
  PRIMARY KEY (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,'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 ');



Thanks to ziesemer for the correct SELECT queries--I learned a lot from them.  Unfortunately, it appears that MySQL doesn't allow DELETE with a subquery, so I converted ziesemer's answer to use JOINS instead.  But I'm a SQL noob, so please correct if these could be improved:
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;
To delete, replace the SELECT line with:
DELETE t1 FROM test t1 INNER JOIN test t2

解决方案
I have these tested, working independently - though the 2nd must be executed after the 1st to get the results you provided in your example.  I am having some difficulty getting them to work as one Select, as the 2nd query is dependent upon the state of the table after the 1st is complete...
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'));
(I'll give a +1 to anyone else who's answer can do this in one query, while producing the same, accurate results - I'm stumped, for now.)

                        
这篇关于使用mysql选择和删除组内的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！